Part II: Working with Formulas and Functions
Calculating number of years and months between dates
In some cases, you’ll be asked to express the difference between two dates in years and
months. In these cases, you can create a text string using two DATEDIF functions.
Cell C4 shown in Figure 12.7 contains the following formula:
=DATEDIF(A4,B4,"Y") & " Years, " & DATEDIF(A4,B4,"YM") & " Months"
FIGURE 12.7
Showing the years and months between dates
We accomplish this task by using two DATEDIF functions joined in a text string with the
ampersand (&) operator.
The first DATEDIF function calculates the number of years between the start and end dates
by passing the year time unit (Y):
DATEDIF(A4,B4,"Y")
The second DATEDIF function uses the YM time unit to calculate the number of months
ignoring the year portion of the date:
DATEDIF(A4,B4,"YM")
We join these two functions with some text of our own to let the users know which number
represents years and which represents months:
=DATEDIF(A4,B4,"Y") & " Years, " & DATEDIF(A4,B4,"YM") & " Months"
Converting dates to Julian date formats
Julian dates are often used in manufacturing environments as a timestamp and quick ref-
erence for batch number. This type of date coding allows retailers, consumers, and service
agents to identify when a product was made and thus the age of the product. Julian dates
are also used in programming, the military, and astronomy.
