Vba excel дата в текст

Excel VBA — Convert Text to Date?

I have a column of dates (column A) stored as text in the format yyyy-mm-dd which I’m trying to convert to dates, ultimately so that I can do lookups against them.

I’ve read a few topics on here and tried some of the suggestions, but I can’t get anything to work. One was using:

This changed the format of the cells to date but didn’t actually change the format of the value which was still stored as text.

My understanding is that I need to use CDate() to change the format of the value from text to date. I’ve tried something like this:

Which gives me a type mismatch error. I wondered if this was because I was trying to save date values back into a non-date formatted cell so I tried combining it with the .NumberFormat = «date» above, which didn’t work either.

Any suggestions would be appreciated.

Создан 04 дек. 13 2013-12-04 12:20:49 user1300244

Hi @user1300244, did you find any answer worth accepting? It is always good to give feedback / rate answers. – sancho.s 19 июн. 17 2017-06-19 06:25:32

Type mismatch error usually means invalid date like ‘»1900-13-12″‘, ‘»1900-02-29″‘, ‘»10000-01-01″‘, etc. Dates before ‘»1899-12-30″‘ result in the generic «Run-time error ‘1004’: Application-defined or object-defined error» – Slai 10 окт. 17 2017-10-10 14:12:09

8 ответов

This assumes that column A contains text values like 2013-12-25 with no header cell.

Создан 04 дек. 13 2013-12-04 12:31:40 Gary’ s Student

You can use DateValue to convert your string to a date in this instance

It can convert yyyy-mm-dd format string directly into a native Excel date value.

Создан 04 дек. 13 2013-12-04 12:48:22 Sam

Besides other options, I confirm that using

works (just tested with the description of your case, with Excel 2010). As for the reasons for you getting a type mismatch error, you may check (e.g.) this.

It might be a locale issue.

Создан 04 дек. 13 2013-12-04 14:05:41 sancho.s

this should be the accepted answer – Orin Moyer 16 янв. 17 2017-01-16 18:03:27

To the OP. I also got a type mismatch error the first time I tried running your subroutine. In my case it was cause by non-date-like data in the first cell (i.e. a header). When I changed the contents of the header cell to date-style txt for testing, it ran just fine.

Hope this helps as well.

Создан 20 фев. 15 2015-02-20 18:10:50 Eric

You can quickly convert a column of text that resembles dates into actual dates with the VBA equivalent of the worksheet’s Data ► Text-to-Columns command.

Bulk operations are generally much quicker than looping through cells and the VBA’s Range.TextToColumns method is very quick. It also allows you the freedom to set a MDY vs. DMY or YMD conversion mask that plagues many text imports where the date format does not match the system’s regional settings. See TextFileColumnDataTypes property for a complete list of the available date formats available.

Caveat: Be careful when importing text that some of the dates have not already been converted. A text-based date with ambiguous month and day integers may already been converted wrongly; e.g. 07/11/2015 may have been interpreted as 07-Nov-2015 or 11-Jul-2015 depending upon system regional settings. In cases like this, abandon the import and bring the text back in with Data ► Get External Data ► From Text and specify the correct date conversion mask in the Text Import wizard. In VBA, use the Workbooks.OpenText method and specify the xlColumnDataType.

Создан 03 июн. 15 2015-06-03 02:11:43 Jeeped

Thanks for this Jeeped. I have tried the first three versions in this thread and yours is the fastest by far. – rohrl77 19 авг. 16 2016-08-19 13:35:54

Функция ДАТАЗНАЧ для преобразования текста в дату в Excel

Функция ДАТАЗНАЧ в Excel предназначена для работы с текстовыми данными в формате ДАТА. Она используется для преобразования текстовых данных в формат Дата и возвращает числовое значение, характеризующее указанную дату.

Как преобразовать дату в текст в Excel

В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.

Зачастую даты в Excel записывают без использования функции ДАТА. Табличный редактор определяет такие значения как обычные текстовые строки. Поэтому процедуры форматирования, сортировки по дате, а также различные вычисления (например, разница дат) приводят к некорректным результатам или появлению ошибок. Поэтому функция ДАТАЗНАЧ полезна для преобразования текстовых значений к данным формата Дата.

Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строки, при этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.

Вид таблицы данных:

Для получения даты в формате, поддерживаемом Excel, используем следующую функцию:

Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

1. Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2). Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
2. Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
3. Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.

Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

Таким образом мы преобразовали текстовые строки в формат даты, которые теперь можно использовать для вычислений в формулах.

Обработка значений даты в текстовом формате в Excel

Пример 2. В таблице Excel указаны даты неверного формата (вместо записи вид «13.06.2019» используется 13_06_2019). Такие данные указаны в двух столбцах. В соседнем необходимо вычислить разницу дней между указанными датами.

Вид таблицы данных:

Для расчетов используем следующую формулу:

Для получения текстовой строки, которая может быть преобразована в данные формата Дата с помощью функции ДАТАЗНАЧ, используем функцию ПОДСТАВИТЬ, которая выполняет замену символов «_» на «.». Результат вычитания двух полученных дат – искомое значение.

Растянем формулу вниз по столбцу чтобы рассчитать все значения:

Особенности синтаксиса функции ДАТАЗНАЧ в Excel

Функция ДАТАЗНАЧ имеет следующую синтаксическую запись:

Единственным аргументом (обязателен для заполнения) является дата_как_текст – текстовое представление даты, которое может быть преобразовано к данным формата Дата. В Excel есть несколько допустимых вариантов записи дат: 13-июн-2019, 13.06.2019. Любой из этих вариантов записи может быть использован в качестве аргумента функции ДАТАЗНАЧ.

1. Если текстовые строки, характеризующие даты, хранятся в ячейках Excel, большинство функций выполняют преобразования данных к требуемому типу автоматически. Однако, во избежание возможных ошибок, рекомендуется использовать функцию ДАТАЗНАЧ.
2. Рассматриваемая функция ориентируется на показания часов, встроенных в ПК, на котором используется редактор Excel. Если в качестве текстового представления даты указана неполная дата, например «13.06», данные о годе будут взяты из текущего времени. Например, функция =ДАТАЗНАЧ(“13.06”) вернет значение 43629, которое после установления формата Дата для ячейки будет преобразовано в 13.06.2019.
3. Если в качестве аргумента функции ДАТАЗНАЧ было передано значение, не преобразуемое к формату Дата (например, =ДАТАЗНАЧ(23), =ДАТАЗНАЧ(ИСТИНА), =ДАТАЗНАЧ(“333”)), будет возвращен код ошибки #ЗНАЧ!
4. Для склеивания значений, содержащихся в отдельных ячейках, чтобы «собрать» их в одну строку, характеризующую значение даты, следует использовать символ “&”. Например, в ячейках A1, B1, C1 хранятся значения 10, 3 и 2019 соответственно. Чтобы получить данные формата Дата и записать их в отдельную ячейку, можно использовать следующую функцию — =ДАТАЗНАЧ(A1&».»&B1&».»&C1).