Hi I know lots of similar questions can be found here, but I am still facing this problem: I have a table with one column called Dates with some values shown below:
12/2/2021
11/2/2021
10/2/2021
9/2/2021
8/2/2021
7/2/2021
6/2/2021
5/2/2021
4/2/2021
3/2/2021
2/2/2021
1/2/2021
31/01/21
30/01/21
When I try to convert it using Format cells either by selecting category as Date or adding custom category as dd/mm/yy, excel treat the dd/mm/YYYY values as mm/dd/yy. So for example, instead of treating 1st row as 12 February, excel thinks of it as 2 December.
I also tried convert text to columns by going to DATA and setting it as DMY but it doesn't solve the problem.
Any help is really appreciated.
Try this formula:
= DATE( YEAR(A1), DAY(A1), MONTH(A1) )
If the values have any space character, try this one:
= DATE( YEAR(TRIM(A1)), DAY(TRIM(A1)), MONTH(TRIM(A1)) )