Обща формула
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Обобщение
За да конвертирате текст в неразпознат формат на дата в правилна дата в Excel, можете да анализирате текста и да съберете подходяща дата с формула, базирана на няколко функции: DATE, LEFT, MID и RIGHT. В показания пример формулата в C6 е:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Тази формула извлича отделно стойностите за година, месец и ден и използва функцията DATE, за да ги събере в датата 24 октомври 2000 г.
Заден план
Когато работите с данни от друга система, може да изпаднете в ситуация, при която датите не са разпознати правилно от Excel, който вместо това третира датите като текст. Например може да имате текстови стойности като тази:
| Текст | Представена дата |
| 20001024 | 24 октомври 2000 г. |
| 20050701 | 1 юли 2011 г. 5 |
| 19980424 | 24 април 1998 г. |
| 28.02.2014г | 28 февруари 2014 г. |
Когато Excel е оценил стойност на датата като текст, една от опциите е да се използва формула за синтактичен анализ на текста на неговите компоненти (година, месец, ден) и да се използват за създаване на дата с функцията DATE. Както беше отбелязано по-горе, препоръчвам ви първо да изпробвате решенията по-долу (добавяне на нула и използване на текст към колони), преди да използвате формула. И двете решения са по-бързи и изискват по-малко усилия.
Обяснение
Функцията DATE създава валидна дата, като използва три аргумента: година, месец и ден:
=DATE(year,month,day)
В клетка C6 използваме функциите НАЛЯВО, СРЕДНО и НАДЯСНО, за да извлечем всеки от тези компоненти от текстов низ и да подадем резултатите във функцията DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Функцията НАЛЯВО извлича най-левите 4 знака за годината, функцията MID извлича символи на позиции 5-6 за месец, а функцията НАДЯСНО извлича най-десните 2 знака като ден. Всеки резултат се връща директно към функцията DATE. Крайният резултат е подходяща дата в Excel, която може да бъде форматирана по какъвто начин искате.
Този подход може да бъде персонализиран според нуждите. Например, неразпознатият формат на дата в ред 8 е dd.mm.yyyy, а формулата в C8 е:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Текст с дълга форма
Понякога може да имате дати в по-дълга форма като „11 април 2020 08:43:13“, които Excel не разпознава правилно. В този случай може да успеете да настроите низа по начин, който позволява на Excel да разпознава правилно датата с функцията ЗАМЕСТИТЕЛ. Формулата по-долу замества втория екземпляр на интервал ("") със запетая и интервал (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
След като добавим запетая след името на месеца, Excel ще разбере датата, но все пак се нуждае от малко „ритане“. Ето защо добавяме нула в края. Операцията по математика кара Excel да се опита да преобразува низа в число. При успех това ще доведе до валидна дата в Excel. Имайте предвид, че може да се наложи да приложите форматиране на номера на датата, за да се покаже правилно датата.
Без формули
Преди да използвате формула за ръчен анализ и конструиране на дата от текст, опитайте една от корекциите по-долу. Първата опция използва математическа операция, за да "подбуди" Excel малко и да го принуди да опита и да оцени текста като число. Тъй като датите в Excel са всъщност числа, това често може да свърши работа. Може да се наложи да приложите формат на дата, ако операциите са успешни.
Добавете нула, за да фиксирате дати
Понякога ще срещнете дати в текстов формат, който Excel трябва да разпознае. В този случай може да сте в състояние да принудите Excel да преобразува текстовите стойности в дати, като добавите нула към стойността. Когато добавите нула, Excel ще се опита да принуди текстовите стойности към числа. Тъй като датите са само числа, този трик е чудесен начин да конвертирате дати в текстов формат, който Excel наистина трябва да разбере.
За да конвертирате дати на място, като добавите нула, опитайте Специално поставяне:
- Въведете нула (0) в неизползвана клетка и копирайте в клипборда
- Изберете проблемните дати
- Специално поставяне> Стойности> Добавяне
- Приложете формат на датата (ако е необходимо)
Можете също да добавите нула във формула като тази:
=A1+0
където А1 съдържа неразпозната дата.
Текст към колони за фиксиране на дати
Друг начин да накарате Excel да разпознава дати е да използвате функцията Text to Columns:
Изберете колоната с дати, след това опитайте Данни> Текст към колони> Фиксирани> Готово
Ако Excel разпознае датите, ще ги поправи всички в една стъпка.
