У меня есть электронная таблица Excel 2010 с большим количеством дат для 2014 года, введенная в следующем формате: Thursday 24th July который мне нужно преобразовать в стандартный формат даты и времени (конечный результат будет импортирован в SQL таблица и даты будут выглядеть как YYYYMMDD поэтому указанная выше дата должна быть 20140724 , я могу справиться с этим преобразованием, но Excel не будет распознавать Thursday 24th July как дату).
1
4 ответа
1
Хорошо, так что это довольно уродливая формула, и я уверен, что кто-то может придумать один намного более чистый. Тем не менее, я проверил это, и это работает, что, я думаю, имеет значение.
В соседнем столбце введите эту формулу:
Где даты начинаются в A2
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
RIGHT($A2,LEN($A2)-FIND(" ",$A2)),"th","",1),"rd","",1),"st","",1"),"nd","",1))
DATEVALUE преобразует дату, хранящуюся в виде текста, в код даты Excel, чтобы ее можно было правильно отформатировать, но дата четверга, 24 июля, имеет некоторые проблемы;
- DATEVALUE не может использовать день недели, и это все равно не имеет значения
- числа
thилиrdв конце дня также являются значениями, которые формула не может использовать - Там нет года хранится в этой дате
Эта формула ничего не может сделать в течение года, но она делает это (работает с середины):
RIGHT($A2,LEN($A2)-FIND(" ",$A2))
Верните номер дня и месяца, узнав, какова длина текстовой даты, и вычтя количество символов между началом и первым пробелом (пробел после названия дня).
SUBSTITUTE([FindText],"th","")
Заменить любой экземпляр th в возвращенной строке ничем
SUBSTITUTE([FirstSubstitute],"rd","")
Заменить любой экземпляр rd в возвращенной строке ничем
=DATEVALUE([StringReplacers])
Преобразовать результат в дату. Если год не указан, будет принят текущий год.
0
Ну, вы можете использовать что-то немного короче:
=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100))*1
Первая часть:
IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))
Извлекает дату. Он пытается извлечь 2 символа после первого пробела первым и, если это число (не возвращает ошибку при умножении на 1), добавляет его во вторую часть, которая является месяцем.
Если это ошибка (например, первые два символа в 1st - это 1s что является недопустимым числом), то это дата меньше 10 и, таким образом, извлекается только один символ.
MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)
Извлекает месяц с предыдущим пробелом.
Затем умножьте все на 1, чтобы получить значение даты. Вы можете отформатировать как yyyymmdd через пользовательское форматирование, и даты будут готовы для импорта в SQL.
Как и в формуле @ CLockeWork, поскольку в формуле не указан год, лист будет иметь текущий год. Если вам нужно указать это, вам придется сделать это вручную, например, так:
=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)&" 2013")*1
За 2013 год.
0
Вы можете использовать эту формулу для преобразования в дату в 2014 году
=(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),"")&2014)+0
который вернет серийный номер даты, формат в требуемом формате даты
Первая функция FIND находит первый пробел в символе 11 или позже (который должен быть вторым пробелом в строке с учетом длины дней недели), а затем, основываясь на этом, REPLACE удаляет два символа непосредственно перед (что будет " st "," th "," rd "или" nd ")
... а затем 2-й FIND находит первый пробел, и REPLACE заменяет весь текст до этого, оставляя вам только 24 июля или аналогичный
Затем вы можете объединить год [2014] до конца и использовать +0, чтобы связать дату
Если вы хотите перейти непосредственно к 20140724 (в виде строки), вы можете использовать эту версию
=2014&TEXT(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),""),"mmdd")
0
Я нашел одно решение для этого во время написания вопроса, поэтому я подумал, что в любом случае отправлю его на случай, если это кому-нибудь поможет:
Это не идеальное решение, однако оно работает. Ряд действий поиска / замены для преобразования дат в то, что Excel поймет. Важно отметить интервал, выполнить поиск, исключая апострофы, но заметьте, если в них есть пробел
- Найти / Заменить, найти
'Monday 'и заменить на'', то же самое для'Tuesday ','Wednesday 'т.д., Чтобы убрать дни с даты. - Найти / заменить, найти
'January'и заменить на'January 2014', то же самое для'February','March'т.д., Чтобы добавить год, который вы хотите - Найти / заменить, найти
'st 'и заменить на' ', то же самое для'nd ','rd 'и'th '
Затем вы можете отформатировать ячейки как даты, и Excel должен их распознать и преобразовать. Правильный интервал в поиске / замене важен, потому что вы не хотите найти 'st' для '1st' и удалить его в конце 'August'