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. Excel Tips - Regional Setting UK

  • 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: Excel Tips - Date1

  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: Excel Tips - Date3

  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.


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


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Basic and tagged . Bookmark the permalink.

1 Response to What we need to know about inputting Date in Excel?

  1. Pingback: Dropdown calendar in Excel | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.