Making Sense of Dates in Excel

Working with dates in Excel can be quite confusing. How to make sense of date entries in Excel? Watch the video below:

Making Sense of Dates in Excel

When we enter the following dates in Excel, we find something very interesting:

Alignment of date data in an Excel worksheet
Making Sense of Date Entries in Excel

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:

Making sense of data - regional format in date and time settings
Making sense of data – regional format in date and time settings

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:

Making Sense of Dates in Excel Using English (United Kingdom) under header Regional Format
Making sense of date entries using English (United Kingdom) under Regional Format
Making sense of date entries using English (United Kingdom) under Regional Format

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:

To do list with progress tracker