Handy hints for Excel

This new regular feature article aims to provide useful tips and tricks for business related software. In this issue we cover Excel’s Date and Time functions.

Dates are stored in Excel as numbers - starting at 01-Jan-1900. So, the date 01/01/07 for example, is actually stored as 39083.

Times are stored as parts of a day as a decimal fraction so, 06:00 is stored as 0.25 and 22:00 is stored as 0.916666666666667!

Display Date and Time

To display the current date and time in a cell, type in the formula: =NOW()

This formula displays both the date and time in the cell. To display just the date, then use the formula: =TODAY()

These formulae update each time the sheet opens. To insert today’s date into a cell and not have it update automatically, press Ctrl and ; together.

Date and Time calculations

	A	B
1	01/11/2006
2	19/02/1980
3	26	26

For example, cell A1 contains today’s date, cell A2 contains a date of birth.

To ascertain age, then one of two different formulae can be used:

The formula in cell A3 is =DATEDIF(A2,A1,”Y”)

The formula in cell B3 is =YEAR(A1)-YEAR(A2). The cell B3 must be formatted to a general number.