Microsoft Office Tutorials and References

In Depth Information

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. So, 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 0 (zero), calling for the “0th” day of the first month. The DATE function inter-

prets this as the day before the first day, or December 31 of the previous year in this example. Similarly, negat-

ive numbers can be supplied for the day argument.

Where’s the DATEDIF function?

In several places throughout this chapter, I refer to the DATEDIF function. You may notice that this function does

not appear in the Insert Function dialog box, is not listed in the Date & Time drop-down list, and does not appear

in the Formula AutoComplete list. Therefore, to use this function, you must always enter it manually.

The DATEDIF function has its origins in Lotus 1-2-3, and apparently Excel provides it for compatibility purposes.

For some reason, Microsoft wants to keep this function a secret. You won't even find the DATEDIF function in the

Help files, although it's available in all Excel versions. Strangely, DATEDIF made an appearance in the Excel

2000 Help files but hasn't been seen since.

DATEDIF is a handy function that calculates the number of days, months, or years between two dates. The func-

tion takes three arguments: start_date, end_date, and a code that represents the time unit of interest. Here's an