Excel VBA – преобразовать текст в дату?

У меня есть столбец дат (столбец A), который хранится в виде текста в формате yyyy-mm-dd, который я пытаюсь преобразовать в даты, в конечном счете, чтобы я мог выполнять поиск по ним.

Я прочитал несколько тем здесь и попробовал некоторые из предложений, но я не могу заставить ничего работать. Один использовал:

Columns("A").Select Selection.NumberFormat = "date" 

Это изменило формат ячеек на сегодняшний день, но фактически не изменило формат значения, которое все еще хранилось в виде текста.

Я понимаю, что мне нужно использовать CDate () для изменения формата значения из текста на сегодняшний день. Я пробовал что-то вроде этого:

 Dim c As Range For Each c In ActiveSheet.UsedRange.columns("A").Cells c.Value = CDate(c.Value) Next c 

Это дает мне ошибку несоответствия типа. Я задавался вопросом, было ли это из-за того, что я пытался сохранить значения даты обратно в неформатную форматированную ячейку, поэтому я попытался объединить ее с .NumberFormat = “date” выше, что тоже не сработало.

Мы ценим любые предложения.

Вы можете быстро преобразовать столбец текста, который напоминает даты в фактические даты, с эквивалентом VBA команды Data ► Text-to-Columns.

 With ActiveSheet.UsedRange.Columns("A").Cells .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat) .NumberFormat = "yyyy-mm-dd" 'change to any date-based number format you prefer the cells to display End With 

Массовые операции, как правило, намного быстрее, чем цикл по ячейкам, а метод VBA Range.TextToColumns очень быстрый. Это также позволяет вам установить маску преобразования MDY или DMY или YMD, которая наносит большой урон текстовому ввозу, если формат даты не соответствует региональным настройкам системы. См. Свойство TextFileColumnDataTypes для получения полного списка доступных доступных форматов даты.

Предостережение: будьте осторожны при импорте текста, который некоторые даты еще не были преобразованы. Текстовая дата с двусмысленными целыми числами месяца и дня может быть неверно преобразована; например, 07/11/2015, возможно, были интерпретированы как 07-Nov-2015 или 11-Jul-2015 в зависимости от региональных настроек системы. В таких случаях откажитесь от импорта и верните текст с помощью данных ► Получить внешние данные ► Из текста и укажите маска преобразования даты в мастере импорта текста. В VBA используйте метод Workbooks.OpenText и укажите xlColumnDataType.

Вы можете использовать DateValue для преобразования вашей строки в дату в этом экземпляре

 Dim c As Range For Each c In ActiveSheet.UsedRange.columns("A").Cells c.Value = DateValue(c.Value) Next c 

Он может преобразовать строку формата yyyy-mm-dd непосредственно в значение даты Excel.

Помимо других вариантов, я подтверждаю, что использование

 c.Value = CDate(c.Value) 

работает (просто протестировано с описанием вашего дела, с Excel 2010). Что касается причин получения ошибки несоответствия типа, вы можете проверить (например) это .

Это может быть проблема локали.

Может быть:

 Sub dateCNV() Dim N As Long, r As Range, s As String N = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To N Set r = Cells(i, "A") s = r.Text r.Clear r.Value = DateSerial(Left(s, 4), Mid(s, 6, 2), Right(s, 2)) Next i End Sub 

Это предполагает, что столбец А содержит текстовые значения, такие как 2013-12-25, без ячейки заголовка.

Этот код работает для меня

 Dim N As Long, r As Range N = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To N Set r = Cells(i, "B") r.Value = CDate(r.Value) Next i 

Попробуйте изменить столбцы в соответствии с вашим листом

Для OP … Я также получил ошибку несоответствия типа при первом запуске вашей подпрограммы. В моем случае это было связано с данными, не относящимися к дате, в первой ячейке (т. Е. Заголовке). Когда я изменил содержимое ячейки заголовка в txt для тестирования даты, он просто отлично …

Надеюсь, это тоже поможет.

Взрыв из прошлого, но я думаю, что нашел легкий ответ на это. Следующие работали для меня. Я думаю, что это эквивалент выбора ячейки, нажимая F2, а затем нажав enter, что делает Excel распознавать текст как дату.

 Columns("A").Select Selection.Value = Selection.Value 

Я избавился от несоответствия типов, выполнив следующий код:

 Sub ConvertToDate() Dim r As Range Dim setdate As Range 'in my case I have a header and no blank cells in used range, 'starting from 2nd row, 1st column Set setdate = Range(Cells(2, 1), Cells(2, 1).End(xlDown)) With setdate .NumberFormat = "dd.mm.yyyy" 'I have the data in format "dd.mm.yy" .Value = .Value End With For Each r In setdate r.Value = CDate(r.Value) Next r End Sub 

Но в моем конкретном случае у меня есть данные в формате «dd.mm.yy»,

Для DD- MM -YYYY это простой способ обхода строк и дат:

вставьте дату в строку через DD- MMM -YYYY, например 01-11-2017 -> 01-Nov-2017

U может использовать FORMAT (дата, «dd- mmm -yyyy») для ввода дат в строку из листа распространения.

Позже, когда вы выводите его из строки, это не будет путать дни и месяцы.

  • В чем разница между .text, .value и .value2?
  • Сохраните каждый лист в книге для разделения файлов CSV
  • Добавить пробел после n-й запятой в Excel, Notepad ++ или emEditor?
  • Как найти последнюю строку, содержащую данные на листе Excel с макросом?
  • Разбор даты и времени ISO8601 (включая TimeZone) в Excel
  • Как объявить глобальную переменную в VBA?
  • Как создать отдельный файл CSV из VBA?
  • Как добавить параметры во внешний запрос данных в Excel, который нельзя отобразить графически?
  • Как скопировать строку в excel с использованием определенного слова и вставить в другой лист Excel?
  • Простая обработка ошибок VBA Excel
  • Как получить старое значение измененной ячейки в Excel VBA?
  • Interesting Posts

    Перекрестно проверять адреса электронной почты и удалять всю строку дубликатов?

    Перенаправление по опции выбора в поле выбора

    Какую платформу разработки .NET Dependency Injection стоит посмотреть?

    Ubuntu + CISCO VPNC CLIENT для моей новой версии ubuntu (32 бит)

    программный код textCursorDrawable

    Максимальные размеры разметки окна после сеанса RDP

    Катум-ромная кривая без острия и самопересечений

    Пользовательский серализатор Gson для одной переменной (из многих) в объекте с использованием TypeAdapter

    Считается ли приемлемым не вызывать Dispose () в объекте задачи TPL?

    Сценарий расписания R с использованием cron

    Какие программы, драйверы, приложения или другое стороннее программное обеспечение мне нужно после чистой переустановки Windows 10?

    Android Java; Как разобрать локальный файл JSON из папки с данными в ListView

    jqGrid DatePicker фильтрация без нажатия клавиши ввода

    ОЗУ ПК непригодным и плоскостным. Как и процессор. Фильмы, которые не играют

    Способ ограничения пропускной способности программ в Windows?

    Давайте будем гением компьютера.