Mastering Dates in Excel
October 27, 2004
Dates in Excel
Dates give a lot of people problems in Excel. Dates are actually stored as the number of days since January 1, 1900.
Here is a cool shortcut: Hit Ctrl + ; to enter today's date in cell A1.
![](/img/tech-tv/MEC04102700.jpg)
You can create formulas for dates. The formula =A1+7
will find a date 7 days from today.
![](/img/tech-tv/MEC04102701.jpg)
Here is one of the frustrations about dates. Sometimes if you enter a formula that should result in a date, Excel will show the answer as a number.
![](/img/tech-tv/MEC04102702.jpg)
Use the Format - Cells dialog to format the cells as dates. There are many different date formats from which to choose.
![](/img/tech-tv/MEC04102703.jpg)
The result:
![](/img/tech-tv/MEC04102704.jpg)
Here is a cool trick for entering the days of the work week. Enter a date in a cell. Right-click the fill handle of the cell, drag, release the mouse, and choose Fill Weekdays.
![](/img/tech-tv/MEC04102705.jpg)
Result - only Monday through Fridays are filled in.
![](/img/tech-tv/MEC04102706.jpg)
Another Trick with the Fill Handle. Right-click the fill handle. Drag. Release. Choose Fill Series.
![](/img/tech-tv/MEC04102707.jpg)
![](/img/tech-tv/MEC04102708.jpg)
![](/img/tech-tv/MEC04102709.jpg)
Time Sheets
There is a subtle problem when you are using a time sheet. If you attempt to total up times exceeding 24 hours, the answer will be wrong. This is because Excel only shows you the times in excess of a complete day.
![](/img/tech-tv/MEC04102709.jpg)
![](/img/tech-tv/MEC04102710.jpg)
To correct this, select Format - Cells. Choose the Custom number format. Instead of a format like h:mm, put the h in square brackets: [h]:mm
![](/img/tech-tv/MEC04102711.jpg)
Summary
Excel's date calculations can be great, if you know the tricks and tips shown above. To get a thorough understanding of Excel, check out the self-paced training CD - Join the Excellers League