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

520
Crops

У меня есть 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 
2
Что вы подразумеваете под условно конвертировать? Покажите условие нам! Rajesh S 5 лет назад 0
@RajeshS То, что я имел в виду под условным, было преобразованием в `dd-mm-yyyy` в соответствии с типом ввода в ячейке. Crops 5 лет назад 0
если просто вы хотите конвертировать входные даты, так как выходные шоу могут быть купированы по формуле TEXT excel. Rajesh S 5 лет назад 0
И что ты имеешь в виду под этим "и извлечь из них год, используя формулу Excel?" так как ваш вывод имеет все 3 Дата, месяц и год! Rajesh S 5 лет назад 0
`TEXT` может использоваться индивидуально, если я знаю формат ввода. У меня есть тысячи ячеек ввода (в нескольких форматах), которые я хочу преобразовать в вывод. Однако я не могу получить вывод. Получив вывод, я могу извлечь год, используя `YEAR ()` Crops 5 лет назад 0
предположим, что вы вводите данные в столбце A, затем напишите это в ** B2 = Text (a2, "DD-MM-YYYY") ** и заполните. Если вы хотите сделать все в самом быстром режиме, то требуется макрос. Rajesh S 5 лет назад 0
Представляет ли этот список все возможные форматы в вашем столбце? Ron Rosenfeld 5 лет назад 0
@RonRosenfeld Да, это все форматы. Crops 5 лет назад 0

2 ответа на вопрос

1
robinCTS

Поскольку ваши входные данные содержат даты до 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]

Заметки:

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

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

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

Если все, что вам действительно нужно, это год, то используйте формулу @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«s

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

OP никогда не требовал VBA, OP нуждается в формуле Excel !! Rajesh S 5 лет назад 0
@RajeshS И это была первая часть ответа. Но я также обсудил другие варианты. Это не просто бесплатный «код для меня», а скорее существует для предоставления информации как ОП, так и другим, которые впоследствии могут искать ответы в БД. Таким образом, оба объяснения алгоритмов, а также альтернативные методы решения проблемы могут быть полезны. Ron Rosenfeld 5 лет назад 1

Похожие вопросы