Teach Yourself Excel Lesson 12 - Dates

Nick's picture


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:


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


  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:

Date Expense type Necessary ? Description Amount
01-Jan-06 Eating Out FALSE pub 20
01-Feb-07 Eating Out FALSE Maccie D's 5

Your sheet should now look like this


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

Training Video on Dates in Excel:

dates.xls28.5 KB