Несколько форматов даты в одном столбце в Excel

Question

У меня есть CSV-файл со столбцом даты с датами в различных формах следующим образом:

dd-mm-yyyy

yyyy-mm-dd

yyyy-mm

yyyy

Эти нерегулярные данные находятся в одном столбце.

Как мне преобразовать их все в dd-mm-yyyy и извлечь из них год, используя формулу Excel? Желаемый результат выглядит следующим образом:

Input       Output
25-03-1954  25-03-1954
22-09-1987  22-09-1987
1990-01-25  25-01-1990
1968-11-15  15-11-1968
1919-01     01-01-1919
1873-02     01-02-1873
1945        01-01-1945
1933        01-01-1933

Answer 1

Поскольку ваши входные данные содержат даты до 1900 года, все становится немного сложнее. Самый простой способ справиться с этим - это работать исключительно с текстовыми представлениями дат.

Если вам на самом деле не нужна полная дата, а просто год, введите эту базовую формулу в любую ячейку в строке 2:2:

=IFERROR(VALUE(LEFT(A2,4)),VALUE(RIGHT(A2,4)))

Для полной даты формула немного сложнее:

=IF(MID(A2,3,1)="-",A2,CHOOSE((LEN(A2)-4)/3+1,"01-01","01"&MID(A2,5,3),RIGHT(A2,2)&MID(A2,5,3))&"-"&LEFT(A2,4))

---

Объяснение:

Предварительно подтвержденная версия формулы полной даты выглядит следующим образом:

=
IF(
  MID(A2,3,1)="-",
  A2,
  CHOOSE(
    (LEN(A2)-4)/3+1,
    "01-01",
    "01"&MID(A2,5,3),
    RIGHT(A2,2)&MID(A2,5,3)
  )
  &"-"&LEFT(A2,4)
)

Это довольно просто. Единственная хитрость - первый аргумент функции CHOOSE() , (LEN(A2)-4)/3+1 . Это отображает длины введенных строк даты в индексы на основе 1, т.е. [yyyy, yyyy-mm, yyyy-mm-dd, dd-mm-yyyy] → [4, 7, 10, 10] → [1, 2, 3, 3] .

Заметки:

• Предварительно подтвержденная формула действительно работает, если она введена.

Предостережения:

• В формулах предполагается, что все введенные даты хранятся в виде текста. (Формулы легко модифицируются для учета дат, хранящихся в виде серийных номеров.)
• Если входные даты содержат начальные / конечные пробелы, приведенные выше формулы могут работать неправильно. Они определенно не будут работать с пробелами (или другими символами) где-либо еще в строках даты. Замена всех A2 в формулах с TRIM(A2) или, что еще лучше, с SUBSTITUTE(A2," ","") , решит эту проблему.

Answer 2

Если все, что вам действительно нужно, это год, то используйте формулу @robinCTS.

Если вы хотите, чтобы результат был "настоящими датами Excel" и

• если ваши данные текстовые, а не реальные даты,
• и если ваши региональные настройки даты - DMY ,
• и столбец отформатирован как dd-mm-yyyy

тогда вы можете использовать эту формулу:

=IFERROR(IFERROR(IFERROR(DATEVALUE(A2),DATEVALUE(A2&"-01")),DATEVALUE(A2&"-01-01")),"illegal date format")

Обратите внимание, что даты до 1900 года не могут быть действительными датами в Excel, но могут быть представлены в виде строк TEXT. Я оставлю это вам, чтобы настроить формулу.

Если ваши результаты должны быть осведомлены на международном уровне для стран, которые имеют другие форматы дат, то решение VBA может быть лучшим. Если это невозможно, можно использовать вспомогательные столбцы для разделения данных и последующей их повторной сборки или очень сложную формулу для того же.

Обратите внимание, что этот UDF будет выводить либо фактическую дату, либо только год, в зависимости от второго необязательного аргумента.

Option Explicit
Function ConvertToDate(S As String, Optional Yr As Boolean = False) As Variant
    Dim V
    Dim dtTemp As Date

V = Split(S, "-")
Select Case UBound(V)
    Case 0
        dtTemp = DateSerial(V(0), 1, 1)
    Case 1
        dtTemp = DateSerial(V(0), V(1), 1)
    Case 2
        If Len(V(0)) = 4 Then
            dtTemp = CDate(S)
        Else
            dtTemp = DateSerial(V(2), V(1), V(0))
        End If
End Select

If Yr = True Then
    ConvertToDate = Year(dtTemp)
Else
    If Year(dtTemp) < 1900 Then
        ConvertToDate = Format(dtTemp, "dd-mm-yyyy")
    Else
        ConvertToDate = dtTemp
    End If
End If

End Function

Оба метода смотрят на первый сегмент и предполагают, что это год, если LEN > 4 , а затем посмотрим, сколько сегментов решит, добавлять ли необходимые -01

Решение VBA будет выводить даты до 1900 года в виде текстовой строки.