When you type “1/5/2015” into Excel, do you know whether it is “1st of May, 2015” or “5th of January, 2015”?
- if your PC’s setting is UK, “1/5/2015” means “1st of May, 2015”;
- if your PC’s setting is US, “1/5/2015” means “5th of January, 2015”
- If yyyy is input last, regional setting applies
- If yyyy is input first, yyyy/MM/dd applies regardless of the regional setting
- Dot (.) can be the separator for some settings (not US/UK)
Quite straight forward.
However, there are cases that people just input D/M for current year. It’s fine, but you really need to pay extra attention, especially if you are working on a d/M/yyyy setting. Why?
The tricky thing is when you do not input yyyy, the date you input will have only 2 portions, e.g. 1/5. Excel will try to resolve it as D/M first (or M/D depends on regional setting mentioned above) and assuming you are inputting date of current year. If it cannot be resolved, Excel treats it as M/Y, assuming 1st date of the month. Confusing? A bit. Let’s see below examples for illustration:
- There is no conflict for resolving the data as D/M for the 1st three lines. Either 5 or 1 is valid month number. Excel treat them as D/M and current year.
- For M that is higher than 12 (note: we don’t have month that is after December), Excel cannot resolve it by D/M. Then M/Y takes place, assuming 1st date of the M/Y. As a result, “5/14” means “1st of May, 2014”
Note: If the cell format is not set to short/long date, you will see “May-14” after you input “5/14”. This “May-14” could be misleading. It means “May 2014”, NOT “14th May” Make sense now? Wait… why “5/30” gives “1st of May, 1930″ while “5/29” gives “1st of May, 2029″? Very good observation. By default, when year is input as 2 digit number, Excel treats 01-29 as 20xx; 30-99 as 19xx.
- Whenever you are uncertain, change the cell format to “Long Date” or “DD/MMMM/ YYYY” in custom format to ensure you have input the date you need
- Whenever possible, do not miss the year; and
- Use 4 digits for year
- Do not use dot (.) as separator
- Last but not least, do not use apostrophe ( ‘ ) to enforce date input as text. If you need special format, do it with custom format.
What do you think? Please leave your comments.🙂