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.

You can create formulas for dates. The formula =A1+7 will find a date 7 days from today.

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.

Use the Format - Cells dialog to format the cells as dates. There are many different date formats from which to choose.

The result:

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.

Result - only Monday through Fridays are filled in.

Another Trick with the Fill Handle. Right-click the fill handle. Drag. Release. Choose Fill Series.

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.

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

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