Dates are just numbers in Excel. To calculate the number of days between two dates, just subtract one from the other (you might find Excel automatically formats the answer as a date, in which case you need to change the number format back to ‘General’ or ‘Number’).
However, it’s also often the case that you need to know the number of whole years or months between two dates. A recent example – has this person turned 17? In this case, you can either write a complicated formula to calculate it, or you can use the Excel function DATEDIF() – read it as DateDif, not DatedIf. Just to be awkward, you won’t find this one in the function library!
DATEDIF has three arguments (stuff in brackets separated by commas):
- Date 1 (earlier date)
- Date 2 (later date)
- Output options
The output options are ‘d’, ‘m’, ‘y’, ‘yd’, ‘ym’, or ‘md’, as shown below
DATEDIF() funtion options - formula bar shows cell D3
There are many practical uses for this function: one recent instance was in determining whether someone was eligible for a particular wage, defined by their age. In the example below, the formula checks whether a person has reached the age of 17. In words,
If there are 17 or more whole years between their birth date and today, show Y, otherwise show N.
DATEDIF() in action - formula bar shows cell B12