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:
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
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:
The following formula uses the DATEDIF function to calculate an age. (See the sidebar, “Where's the
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:
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