Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

Note that this function doesn’t calculate
full
years. For example, if cell A1 contains 12/31/2010 and

cell B1 contains 01/01/2011, then the formula returns a difference of one year, even though the

dates differ by only one day.

You can also use the YEARFRAC function to calculate the number of years between two dates.

This function returns the number of years, including partial years. For example

=YEARFRAC(A1,B1,1)

Because the YEARFRAC function is often used for financial applications, it uses an optional third

argument that represents the “basis” for the year (for example, a 360-day year). A third

argument of 1 indicates an actual year.

Calculating a person’s age

A person’s age indicates the number of full years that the person has been alive. The formula in

the previous section (for calculating the number of years between two dates) won’t calculate this

value correctly. You can use two other formulas, however, to calculate a person’s age.

The following formula returns the age of the person whose date of birth you enter into cell A1.

This formula uses the YEARFRAC function:

=INT(YEARFRAC(TODAY(),A1,1))

The following formula uses the DATEDIF function to calculate an age. (See the sidebar, “Where’s

the DATEDIF function?”)

=DATEDIF(A1,TODAY(),”y”)

Determining the day of the year

January 1 is the first day of the year, and December 31 is the last day. But what about all of those

days in between? The following formula returns the day of the year for a date stored in cell A1:

=A1-DATE(YEAR(A1),1,0)

The day argument supplied is zero, calling for the “0th” day of the first month. The DATE

function interprets this as the day before the first day, or December 31 of the previous year in this

example. Similarly, negative numbers can be supplied for the day argument.