Dates

Download starting Sheet

A lesson on Dates in Excel is a very important one as understanding Dates is crucial to writing an  effective spreadsheet.

A Date in Excel is actually a number.

This is ingenious as Excel cleverly displays a date as a date, but stores it as a number. This is particularly useful when you need to add or subtract days from a date. To add a week, just add 7 to the date!

To demonstrate dates are numbers:

1. Type 1 in a cell
2. Right click on the cell => Format cells
3. Choose "Custom", then type: dd-mmm-yyyy in the box provided
4. You'll now see that as far as Excel is concerned, time starts on 01-Jan-1900

Lets have a look at today's date:

1. Select an empty cell
2. Press CTRL + ;  (this enters Today's date in a cell)
3. Select the cell, Press CTRL + SHIFT + #  (this formats a cell as a number)
4. You'll see 39965  (1st June 2009)

We can also easily switch back from a date to a number:

1. Now type: 01-Jan-1900 in a cell
2. Press CTRL + SHIFT + #
3. Excel will now show 1

Entering Dates

Excel offers good flexibility when entering dates too.. Try all of these now:

1. If you type: =TODAY()   Today's date will be returned.
2. Type:  2/1, and you'll get 2nd Jan 2009.
   • Excel assumes that Number/Number is a date in the current year.
3. Type: 1/Jun    =>  1st June 2009
   • Excel assumes that Number/3 letter Month is a date in the current year.
4. Type:  1/Jun/10   =>  1st June 2010
5. Type:  1/Jun/2010   =>  1st June 2010

Formatting Dates

You can format a date in a lot of different ways..

Here are a few ways you can format 1st June 2009

To practise, right click on a cell => format cells => Custom, and type the following in the box provided:

1. dd-mmm-yy    =>  01-Jun-09
   • This is the best date format if you don't care about knowing the day of the week.
2. ddd dd-mmm-yy    =>  Mon 01-Jun-09
   • This will display the day of the week which can be very useful.
3. dd-mmm    =>  01-Jun
   • Use this if you don't care about the year
4. mmm-yy    =>  Jun-09
   • Use this if you only care about the month and year
5. mmm    =>  Jun
   • Use this if you only care about the month

Date Do's and Don'ts:

Do's

• Use the smallest date format for your needs
  • Why use 2009 if 09 will do - We know we're in the 21st century now !
  • This saves column space
• If the day of the week is useful information, display it.
• Use "-" instead of "/"    it looks better.

Don'ts

• Don't ever use mm/dd/yyyy  (often known as "American Date format")
  • Take: 01/02/2009  - If I see that, but don't know the date format, I have no clue whether it's 1st Feb 2009 or 2nd Jan 2009
• Don't use "."  as your separator.
• Don't enter the day of the week.. Excel won't understand:  Thu 01-Feb-07.. just type: 01-Feb-07 and format the cell as ddd dd-mmm-yy

Let's go back to our Expense tracker, and format the date column

Steps:

1. Select A1
2. Press CTRL + SPACE
3. Right click => Format cells
4. Custom
5. Type: ddd dd-mmm-yy  in the box, and press OK
6. Press in sequence:  ALT then O then C then A (to increase the column width)

Now enter the following additional expenses:

Your sheet should now look like this

Next Lesson:  Teach Yourself Excel Lesson 13 - Enter A Formula

Training Video on Dates in Excel: