The date format of MMDDYYYY is a potential problem for many people the world round. The main reason for this is that most people don’t understand how Excel handles dates. But first the background.
Table of contents
Who uses MMDDYYYY
Per Wikipedia the only country that uses MDY exclusively is the United States. Everyone else pretty much uses DMY in some way.
The problem is the best software (Excel is by far the best:) ) tends to come out of the States or is built for the States so the default setting is often MDY.
Why is MMDDYYY a problem
It is not a problem if computers know that what they are being given is a date. Most systems have a method where dates are treated exactly the same no matter what the preferred format is.
The problem arises when a computer is given some text e.g. 01/05/2016 and then is asked to convert this to a date (and the most common place this is done is in Excel).
Is the above date the 1st May 2016 or the 5th Jan 2016. Even worse would be text that looks like this – 01/05/10.
This could be
- 1st May 2010, or
- 5th Jan 2010, or
- 10th May 2001, or
- 5th October 2001.
Excel and Dates
Excel has a special way it treats dates and once you understand this, the format of the date will cause you less hassles.
Everything about Excel dates is based on the number of days from 1 Jan 1900.
If you give it a date (and Excel understands that you have given it a date), it will convert it into a number representing the number of days from the 1st Jan 1900.
How you want it to appear to you is irrelevant to Excel. Whether you use DDMMYYYY or MMDDYYYY, Excel still sees a number (format a date as General and you can see the number).
In other blog posts we have covered some of the typical issues that occur with dates e.g.
- Safest way to enter a date in Excel
- Excel date format not changing
- Excel sort date order
- Mixed date format in excel
We also spend lots of time in the courses covering this. Understanding this one little thing about Excel will save you tons of time!