What we need to know about inputting Date in Excel?

When you type “1/5/2015” into Excel, do you know whether it is “1st of May, 2015” or “5th of January, 2015”?

The answer: It depends! Depends on what?   Depends on the Region and Language Setting in your PC.

• 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”

It worths a deeper look with the following examples of some common ways to input dates, with three different settings presented:

1. If yyyy is input last, regional setting applies
2. If yyyy is input first, yyyy/MM/dd applies regardless of the regional setting
3. 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:

1. 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.
2. 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.

Suggestions:

• 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. 🙂