I’ve been using Microsoft Excel for years now. I’ve also taught many Excel courses. It’s an extremely powerful piece of software that I use everyday for many uses including programming and organizing simple data.

It’s strength lies in it’s over 200 formulas and using them together. The biggest issues I see however with users of the software is how they enter data in Excel. Excel works great, when you give it information in the format that it likes, but all things will fail when Excel can’t figure out what you’re trying to do.

Today we’ll be looking at a very basic and simple thing that EVERYONE needs the know when working with Excel. How to enter dates properly.

[UPDATE]: Another factor was brought to my attention that I have glanced over. The date input format is also based on your computer’s Regional Settings. My tutorial is based on your computer being setup as English (Canada) and not English (US). You can verify your setting in Control Panel -> Regional Settings and Language. If you use the 3rd or 4th format examples below however, you’ll be alright in either language setting

Download Excel 2007 sample file

Dates are very powerful pieces of data in Excel. You can sort by date, view only selected data based on the time frame it fell in and create numerous other calculations based on dates.

Excel has many different ways it will accept a date format, but some how one of the biggest issues I find in all users spreadsheets when I’m trying to help them out is the way they entered the date (or what they thought was a date).

Here are the 4 most accepted input formats Excel will like for the English (Canada) regional setting:

• dd/mm/yyyy (15/02/2011)
• yyyy/mm/dd (2011/02/15)
• Feb 15, 2011 (important: note the comma and space after the day)
• February 15, 2011 (important: note the comma and space after the day)

Notice in the first 2 formats that the month is in the middle. That is VERY important. Placing the month first or last is not valid and will not create a date recognized by Excel.

You might also noticed I always entered the year as a 4 digit number. While this is not required, if you enter the date only as a 2 digit number, the format will be read as the first example of dd/mm/yy (15/02/11) and NOT yy/mm/dd. I don’t use that format when entering dates just to ensure I’m entering the proper date. When I’m manually typing in dates, I actually use the 3rd format all the time. It’s simple and can’t be mistaken.

no cell alignment selected

Here’s another tip. Number and dates (because dates are actually numbers stored in Excel) always align by default to the right no matter what setting they are selected to display in. Text and formulas on the other had always line up by default to the left. The alignment can be overwritten by manually aligning the cells selecting one of the alignment options.

I hope this helps. Don’t forget to leave your comments below and suggestions for other tutorials. Share on Facebook, Tweet it, Google + etc. Share the knowledge.