Преобразование даты из мм / дд / гггг в ее числовое значение

Question

Я получаю данные из инструмента отчетности. Отчет содержит столбцы « Date и « User (в указанном порядке). Инструмент возвращает даты в формате мм / дд / гггг.

Excel не преобразует импортированные данные в числовую дату; оно сохраняется в виде текстового значения (необработанные данные из инструмента отчетности выровнены по левому краю, а не по правому).

Мне нужно объединить User и числовое значение даты (в таком порядке). Использование необработанных импортированных данных дает результат в форме userAmm/dd/yyyy вместо необходимого формата, такого как userA42379 . Объединение даты в виде неформатированного числового значения происходит корректно только в том случае, если исходные данные представлены в формате дд / мм / гггг (в противном случае входные данные не интерпретируются как дата).

Я пытался преобразовать даты из формата мм / дд / гггг в формат дд / мм / гггг, используя MID / LEFT / RIGHT и форматирование чисел в Excel, но это не сработало. Дата по-прежнему отображается в формате мм / дд / гггг при объединении с другой ячейкой с текстом в ней.

Есть идеи?

Answer 1

Вы сказали, что отчет содержит столбцы Date и User (в таком порядке).

Вы также сказали, что инструмент возвращает даты в текстовом формате в формате mm/dd/yyyy . Допустим, в этом случае у вас есть следующее:

Вам необходимо объединить значение User и Date (в указанном порядке). Это то, что мы собираемся сделать в ячейке C2

Теперь я не понимаю, каким образом вы хотите, чтобы формат объединенных дат был. Вы хотите, чтобы в mm/dd/yyyy или dd/mm/yyyy вы хотели, чтобы код даты выдавал вывод, такой как userA 42379?

Я расскажу все три.

Для объединения при сохранении формата даты (mm/dd/yyyy) используйте следующую формулу

=B2&" "&A2

Символ &" "& в середине добавляет пробел между ними.

Для объединения даты в формате (dd/mm/yyyy) используйте следующую формулу

=B2&" "&MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,4)

Для объединения кода даты, такого как 42379 , используйте следующую формулу

=B2&" "&DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))

Answer 2

Вот общее решение, которое будет работать для любого ^* формата даты, начинающегося с месяца, независимо от того, хранится ли он в виде текстового значения или правильного значения даты / времени (серийной даты):

Введите следующую формулу в D2 и ctrl-enter/copy-paste/fill-down в оставшуюся часть столбца:

=TEXT(IF(ISNUMBER(C2),C2,DATE(RIGHT(SUBSTITUTE(C2,"/","  "),4),LEFT(SUBSTITUTE(C2,"/","  "),3),MID(SUBSTITUTE(C2,"/","  "),4,3))),"dd/mm/yyyy")

Результат сохраняется в виде текстового значения. Если требуется ввести серийный номер даты, просто опустите функцию TEXT() и установите для числового формата ячейки значение dd/mm/yyyy

---

Если исходным форматом даты всегда является mm/dd/yyyy то формула упрощается до следующего:

=TEXT(IF(ISNUMBER(C2),C2,DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2))),"dd/mm/yyyy")

---

Для вашего конкретного случая, когда вы генерируете объединенный столбец UserDate , просто опустите функцию TEXT() :

="UserName"&IF(ISNUMBER(C2),C2,DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2)))

Если дата всегда сохраняется в виде текстового значения, формулу можно еще больше упростить:

="UserName"&DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2))

Answer 3

Выберите даты в диапазоне вручную или используя опцию поиска, формат и дату, а затем что-то вроде astrick/astrick/2018.

Измените формат всего найденного на dd/mm/yyyy , если это то, что вы хотели.

Я попробовал =CONCATENATE и он дает мне одно и то же числовое значение с dd/mm/yy и mm/dd/yy поэтому изменение формата не оказывает никакого численного влияния.

Чтобы он отображался в формате даты вместо числового в функции « Concatenate мне нужно было ввести "&TEXT(A4,"mm/dd/yy") где A4 - это место проверки моей даты. Это позволило ему показать что-то вроде даты в A4 mm/dd/yyyy

Sample

Row A1 Japan
    A2 June
    A3 02/07/2018

=CONCATENATE("The date in ", A1, " is ", ""&TEXT(A3,"mm/dd/yy"), " currently.")

Выходные данные = The date in Japan is 07/02/18 currently .

Надеюсь это поможет. Я думаю, это то, что вы ищете.

Answer 4

Я не уверен, как это будет работать для других, но это то, что сработало для меня.

=IFERROR(DATEVALUE(MID($Q4,4,2)&"/"&LEFT($Q4,2)&"/"&RIGHT($Q4,4)),"")

Answer 5

Проверьте снимок экрана:

В ячейке M1 Запишите эту формулу, преобразует дату из формата ММ / ДД / ГГГГ в ДД / ММ / ГГГГ.

=IF(ISNUMBER(K1),TEXT(K1,"DD/MM/YYYY"),K1)

Отредактировано: дата в формате ММ / ДД / ГГГГ как текст в дату в формате ДД / ММ / ГГГГ.

Вы можете использовать эту формулу: =TEXT(DATEVALUE(A97),"dd/mm/YYYY")

Обратите внимание, измените адрес ячейки в обеих формулах в соответствии с вашими потребностями.