Teach Yourself Excel Lesson 12 - Dates
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:
- Type 1 in a cell
- Right click on the cell => Format cells
- Choose "Custom", then type: dd-mmm-yyyy in the box provided
- 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:
- Select an empty cell
- Press CTRL + ; (this enters Today's date in a cell)
- Select the cell, Press CTRL + SHIFT + # (this formats a cell as a number)
- You'll see 39965 (1st June 2009)
We can also easily switch back from a date to a number:
- Now type: 01-Jan-1900 in a cell
- Press CTRL + SHIFT + #
- Excel will now show 1
Excel offers good flexibility when entering dates too.. Try all of these now:
- If you type: =TODAY() Today's date will be returned.
- Type: 2/1, and you'll get 2nd Jan 2009.
- Excel assumes that Number/Number is a date in the current year.
- Type: 1/Jun => 1st June 2009
- Excel assumes that Number/3 letter Month is a date in the current year.
- Type: 1/Jun/10 => 1st June 2010
- Type: 1/Jun/2010 => 1st June 2010
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:
- dd-mmm-yy => 01-Jun-09
- This is the best date format if you don't care about knowing the day of the week.
- ddd dd-mmm-yy => Mon 01-Jun-09
- This will display the day of the week which can be very useful.
- dd-mmm => 01-Jun
- Use this if you don't care about the year
- mmm-yy => Jun-09
- Use this if you only care about the month and year
- 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
- Select A1
- Press CTRL + SPACE
- Right click => Format cells
- Type: ddd dd-mmm-yy in the box, and press OK
- 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-Feb-07||Eating Out||FALSE||Maccie D's||5|
Next Lesson: Teach Yourself Excel Lesson 13 - Enter A Formula
Training Video on Dates in Excel: