I have some dates in Text format and some other in Date format.
I tried so many things to reconvert them in proper format. DD/MM/YYYY
16/10/2022
05/12/2023
24/05/2023
I still cant figure out. They all looks in Date format but some are not. How i can re-format properly. There is sample of my dates.
https://docs.google.com/spreadsheets/d/1aNsoqXnpNkQs7LloFuHVObARqI2QEh5X8ClCu42ICyQ/edit?usp=sharing
How i can fix this?
Here's what happened :
you had a bunch of dates formatted as DD/MM/YYYY and pasted them and they were automatically parsed in the format MM/DD/YYYY leading some of them being recognized as proper dates (but not the ones you intend to have), and some of them being leaved as text.
To solve this you can copy paste the column in a text file, then import this file; when you import the data in excel, use the "text to columns" to choose the date format.
You can also change your locale date settings.
Last solution that does not involve creating a new file : copy paste your dates in a text file, add a ' in front of each line which ensures the cell content is not automatically interpreted, then parse the date from this text using the formula :
=date(right(H2,4),mid(H2,4,2),left(H2,2))
(if your text is in h2)
One last thing : some of your data in the left column is not in the correct format. You probably have a formatting problem / data quality problem in the source data you copy/pasted from.
