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

Question

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

Май-31-2014.

Я пытаюсь найти способ преобразовать это в полезный формат даты 31.05.2014. Есть идеи?

Answer 1

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

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

Это преобразует дату из формата 31 мая 2014 года в формат 31.05.2014. Обратите внимание, что в моем конкретном случае месяцы вводились только тремя буквами (например, июль)

Спасибо!

Answer 2

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

Первая задача - разделить вашу дату на 3 части: год, месяц, день. Если у вас есть эти три, тогда просто собрать их вместе.

Это, конечно, при условии, что ваши даты всегда следуют за скорее идиотским американским форматом даты © MMM-dd-yyyy.

Это было сделано с помощью страницы 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, следующий за форматом глупой американской даты ™:

   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 3

Еще один простой подход заключается в строку , как Май-31-2015 и превратить его в 31-май-2015. Это преобразование позволит нам использовать формулу DATEVALUE() .

Итак, с данными в A1, в B1 введите:

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