Текст Excel для преобразования формата даты

Question

У меня есть пакет данных с несколькими столбцами, в которых дата отформатирована следующим образом:

31 мая 2014 г.

Я пытаюсь найти способ преобразовать это в удобный формат даты 05/31/2014. Есть идеи?

Answer 1

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

Первая задача — разбить дату на 3 части: год, месяц, день. Как только у вас есть эти три части, их легко собрать обратно.

Конечно, это предполагает, что ваши датывсегдаследуйте довольно идиотскому американскому формату дат© МММ-дд-гггг.

Это было сделано с использованием страницы MicrosoftРазделение текста между столбцами с помощью функцийВ качестве примера.

1. Разделение месяца:
   это достигается с помощью searchфункции поиска первого месяца -и инструмента разделения, Leftчтобы вырезать его.

   =LEFT(A1, SEARCH("-",A1,1)-1)

   что следует Mayиз вашего примера.

2. Разделение дня:
   Похоже, но MIDтребуется гораздо более сложное использование функции. Нам нужно найти первое и второе, -затем вычесть местоположение первого из второго, чтобы получить длину требуемого текста, в результате получается довольно длинная функция:

   =MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

   возвращает SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)длину, в то время как SEARCH("-",A1,1)+1просто получает первое местоположение. MIDИнструмент вырезает их и возвращается 23из вашего примера.

3. Разделение года:
   Последний инструмент, который нам нужен, — это RIGHTинструмент.

   =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

   Он просто получает длину строки, затем дважды просматривает ее и возвращает все, что осталось (или справа).

Проблема в том, что теперь у вас все еще есть месяц в непригодном для использования текстовом формате. Чтобы сделать его пригодным для использования, нам нужно использовать инструмент DATEVALUEдля его преобразования. Этот инструмент не принимает месяц сам по себе, но принимает строку MMM YY. Что мы можем сделать, так это преобразовать наш текст месяца в число после того, как мы присоединим к нему год.

4. Получение месяца в виде числового значения:
   ради аргумента предположим, что мы сохранили нашу первую функцию в ячейке B1.
   DATEVALUE(B1 &" 01")Она вернет строку значения даты, которую Excel использует внутри себя, затем мы можем обернуть ее в MONTHфункцию, чтобы вернуть числовое значение для месяца:

   MONTH(DATEVALUE(B1 &" 01"))

5. Затем мы можем объединить все это в одну DATE(year,month,day)функцию и таким образом преобразовать ее в настоящую дату, которую может использовать Excel. Месяц находится в ячейке B1, день в ячейке C1, а год в D1, следуя Stupid American Date Format™:

   DATE(D1, B1, C1)

   затем вернётся 23/05/2014в камеру.

6. Если вы хотите чего-то действительно необычного, вы можете объединить все вышеперечисленные формулы в одну.

=DATE(RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)),MONTH(DATEVALUE(LEFT(A1, SEARCH("-",A1,1)-1)&"  1")),MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1))

Answer 2

Другой простой подход — взять строку типа31 мая 2015 г.и преобразовать его в31-мая-2015. Это преобразование позволит нам использоватьДАТАЗНАЧ()формула.

Итак, с данными вА1, вВ1входить:

=DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

Answer 3

Я знаю, что на этот вопрос уже ответили, и я ценю все вклады. Я хотел бы добавить еще один вариант на всякий случай, если он кому-то поможет:

=DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4)))

Это преобразует дату из формата May-31-2014 в формат 5/31/2014. Обратите внимание, что в моем конкретном случае месяцы были введены только тремя буквами (например, Jul)

Спасибо!