Excel текст в формат даты

Question

Я хочу преобразовать текстовую строку (которая импортируется в Excel как поле формата «Общие») 31.12.2017 в формат даты, отображающий ее как; дд / мм / гггг, однако все попытки пока не увенчались успехом, в т.ч. параметр text to columns, переформатируя его как поле даты, используя английский (Великобритания) в качестве локали.

Я также попытался проанализировать текст с помощью формулы, основанной на: DATE, LEFT, MID и RIGHT Functions; но безрезультатно. Проблема заключается в включении / в текстовой строке и в том, что я хочу поменять формат с мм / дд / гггг на дд / мм / гггг. Кто-нибудь испытывал эту проблему?

Answer 1

С текстом в A1, в B1 введите:

=DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),TRIM(MID(SUBSTITUTE($A1,"/",REPT(" ",999)),2*999-998,999)))

и примените правильный формат:

Answer 2

Я только что подтвердил догадку Дэйва на моем собственном компьютере, что настройки часов машины не только влияют на Excel, они ограничивают способность Excel понимать даты, отличные от формата часов компьютера.

Лично я установил, что мои часы Windows показывают dd-mm-yy, хотя я нахожусь в США. Поэтому, когда я ввожу «31.12.2017», Excel не может понять, что это дата, независимо от того, что я пытаюсь. Изменение формата числа на Короткую дату, Длинную дату или Число не имеет значения, это все еще строка. Подчинение его таким функциям, как DATEVALUE(), VALUE() или TEXT(«31.12.2017», «dd /mm /yyyy») возвращает #VALUE! ошибка.

Но чтобы я не оставил вас ни с чем, я собрал не очень красивую, но работающую формулу, которая будет применяться к вашим клеткам:

где

A1 ="12/31/2017"
A2 =LEFT(A1,FIND("/",A1)-1) [month]
A3 =MID(A1,FIND("/",A1)+1,(FIND("/",A1,FIND("/",A1)+1))-(FIND("/",A1)+1)) [day]
A4 =RIGHT(A1,4) [year]

вы бы объединить как

=CONCATENATE(A3,"/",A2,"/",A4)

для общей суммы формулы

=CONCATENATE(MID(A1,FIND("/",A1)+1,(FIND("/",A1,FIND("/",A1)+1))-(FIND("/",A1)+1)),"/",LEFT(A1,FIND("/",A1)-1),"/",RIGHT(A1,4))