Part II: Working with Formulas and Functions
It’s important to note that both of these functions will automatically recalculate each time
you change or open your workbook, so don’t use these functions as a timestamp of record.
If you want to enter a static date that won’t change, press Ctrl+; (semicolon) on your keyboard. This will insert a static
date in the active cell.
You can use the TODAY function as part of a text string by wrapping it in the TEXT func-
tion with some date formatting. This formula will display text that will return today’s date
in Month Day, Year format.
="Today is "&TEXT(TODAY(),"mmmm d, yyyy")
For more details on using the TEXT function, see the section called “Formatting the numbers in a text
string” in Chapter 11.
Calculating age
One of the easiest ways to calculate the age of anything is to use Excel’s DATEDIF function.
This function makes calculating any kind of date comparisons a breeze.
To calculate a person’s age using the DATEDIF function, you can enter a formula like this:
=DATEDIF("5/16/1972",TODAY(),"y")
You can, of course, reference a cell that contains a date:
=DATEDIF(B4,TODAY(),"y")
The DATEDIF function calculates the number of days, months, or years between two dates.
It requires three arguments—a start date, an end date, and a time unit.
The time units are defined by a series of codes listed in Table 12.3.
TABLE 12.3 DATEDIF Time Unit Codes
Code What It Returns
"y" The number of complete years in the period.
"m" The number of complete months in the period.
"d" The number of days in the period.
"md" The difference between the days in start_date and end_date. The months and years
of the dates are ignored.
"ym" The difference between the months in start_date and end_date. The days and years
of the dates are ignored.
"yd" The difference between the days of start_date and end_date. The years of the
dates are ignored.
