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" выше, что тоже не сработало.

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

Источник

9 ответов

Ты можешь использовать 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.

Источник

Вы можете быстро преобразовать столбец текста, который напоминает даты, в фактические даты с помощью VBA-эквивалента команды Данные ► Текст в столбцы на рабочем листе.

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

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

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

Источник

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

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

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

Источник

У меня была очень похожая проблема ранее. К сожалению, я посмотрел эту ветку и не нашел ответа, которым был бы доволен. Надеюсь, это поможет другим.

С помощью VBA.DateSerial(year,month,day) Вы можете преодолеть внутреннее смещение Excel в формат даты США. Это также означает, что вы имеете полный контроль над данными, что я лично предпочитаю:

function convDate(str as string) as Date
  Dim day, month, year as integer
  year  = int(mid(str,1,4))
  month = int(mid(str,6,2))
  day   = int(mid(str,9,2))
  convDate = VBA.DateSerial(year,month,day)
end function
Источник

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

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, а затем нажатию клавиши ввода, что позволяет Excel распознавать текст как дату.

Columns("A").Select
Selection.Value = Selection.Value
Источник

Решил проблему для меня:

    Range(Cells(1, 1), Cells(100, 1)).Select

    For Each xCell In Selection

    xCell.Value = CDate(xCell.Value)

    Next xCell
Источник

Я избавился от несоответствия типов с помощью следующего кода:

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 есть простой обходной путь для управления строкой и датами:

вставьте дату в строку с помощью ДД-МММ- ГГГГ, например, 01-11-2017 -> 01-Nov-2017

Вы можете использовать FORMAT (date, "dd-mmm-yyyy") для ввода дат в строку из электронной таблицы.

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

Источник
Другие вопросы по тегам