Working with dates in Excel can be quite confusing. How to make sense of date entries in Excel? Watch the video below:
When we enter the following dates in Excel, we find something very interesting:
The dates from 1/1/2022 to 12/1/2022 are aligned right and represent 1-January-2022 to 1-December-2022. The date entries from 13/1/2022 to 17/1/2022 are not parsed or understood by Excel because we don’t have a 13th month or 17th month! Also, the dates are left aligned as though such dates are treated as text by Excel. If we check the ‘Date and time Settings’, we will note that current format under the ‘Regional Format’ heading will be ‘English (United States)’ as shown in the image below:
Now if you change the above current format under Regional format to ‘English (United Kingdom)’ and enter the dates again, everything falls in place and we have the situation as shown in the images below:
Another method to convert the dd/mm/yyyy date format to mm/dd/yyyy is to use the ‘Text to Column’ method as described in the video.
The moral of the story: It’s best to use the mm/dd/yyyy USA date format in Excel!
Further Reading: