Formula generică
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
rezumat
Pentru a converti textul într-un format de dată nerecunoscut într-o dată Excel adecvată, puteți analiza textul și asambla o dată adecvată cu o formulă bazată pe mai multe funcții: DATA, STÂNGA, MID și DREAPTA. În exemplul prezentat, formula din C6 este:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Această formulă extrage valorile de an, lună și zi separat și folosește funcția DATE pentru a le asambla la data de 24 octombrie 2000.
fundal
Când lucrați cu date dintr-un alt sistem, s-ar putea să vă confruntați cu o situație în care datele nu sunt recunoscute în mod corespunzător de Excel, care în schimb tratează datele ca text. De exemplu, este posibil să aveți valori de text de genul acesta:
| Text | Data reprezentată |
| 20001024 | 24 octombrie 2000 |
| 20050701 | 1 iulie 20115 |
| 19980424 | 24 aprilie 1998 |
| 28.02.2014 | 28 februarie 2014 |
Când Excel a evaluat o valoare a datei ca text, o opțiune este să utilizați o formulă pentru a analiza textul în componentele sale (an, lună, zi) și să le utilizați pentru a face o dată cu funcția DATE. După cum sa menționat mai sus, vă recomand să încercați mai întâi soluțiile de mai jos (adăugarea zero și utilizarea textului în coloane) înainte de a utiliza o formulă. Ambele soluții sunt mai rapide și necesită mai puțin efort.
Explicaţie
Funcția DATE creează o dată validă utilizând trei argumente: an, lună și zi:
=DATE(year,month,day)
În celula C6, folosim funcțiile LEFT, MID și RIGHT pentru a extrage fiecare dintre aceste componente dintr-un șir de text și pentru a alimenta rezultatele în funcția DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funcția STÂNGA extrage cele mai stângi 4 caractere pentru an, funcția MID extrage caractere în pozițiile 5-6 pentru lună, iar funcția DREAPTA extrage cele mai stricte 2 caractere ca zi. Fiecare rezultat este returnat direct la funcția DATE. Rezultatul final este o dată Excel adecvată, care poate fi formatată în orice fel doriți.
Această abordare poate fi personalizată după cum este necesar. De exemplu, formatul de dată nerecunoscut din rândul 8 este dd.mm.yyyy și formula din C8 este:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Text de formă lungă
Uneori este posibil să aveți date într-o formă mai lungă, cum ar fi „11 aprilie 2020 08:43:13”, pe care Excel nu o recunoaște corect. În acest caz, este posibil să puteți regla șirul într-un mod care să permită Excel să recunoască corect data cu funcția SUBSTITUTE. Formula de mai jos înlocuiește a doua instanță a unui spațiu ("") cu o virgulă și un spațiu (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Odată ce adăugăm virgula după numele lunii, Excel va înțelege data, dar are încă nevoie de o mică „lovitură”. De aceea adăugăm zero la final. Operația matematică face ca Excel să încerce să convertească șirul într-un număr. Când va avea succes, aceasta va avea ca rezultat o dată Excel validă. Rețineți că poate fi necesar să aplicați formatarea numărului de dată pentru a afișa corect data.
Fără formule
Înainte de a utiliza o formulă pentru a analiza și construi manual o dată din text, încercați una dintre remedierile de mai jos. Prima opțiune folosește o operație matematică pentru a „împinge” puțin Excelul și a-l forța să încerce să evalueze textul ca număr. Deoarece datele Excel sunt de fapt numere, acest lucru poate face adesea trucul. Poate fi necesar să aplicați un format de dată dacă operațiunile reușesc.
Adăugați zero pentru a stabili datele
Uneori, veți întâlni date într-un format text pe care Excel ar trebui să îl recunoască. În acest caz, este posibil să puteți forța Excel să convertească valorile textului în date adăugând zero la valoare. Când adăugați zero, Excel va încerca să constrângă valorile textului la numere. Deoarece datele sunt doar numere, acest truc este o modalitate excelentă de a converti datele în format text pe care Excel ar trebui să le înțeleagă.
Pentru a converti datele în loc adăugând zero, încercați Lipire specială:
- Introduceți zero (0) într-o celulă neutilizată și copiați în clipboard
- Selectați datele problematice
- Lipiți special> Valori> Adăugați
- Aplicați un format de dată (dacă este necesar)
De asemenea, puteți adăuga zero într-o formulă ca aceasta:
=A1+0
unde A1 conține o dată nerecunoscută.
Trimiteți text către coloane pentru a stabili datele
O altă modalitate de a face ca Excel să recunoască datele este utilizarea funcției Text în coloane:
Selectați coloana de date, apoi încercați Date> Text în coloane> Fix> Finish
Dacă Excel recunoaște datele, le va rezolva pe toate într-un singur pas.
