Suppose the date of 28/07/2009 and 05/08/2009. Between these two dates, there are only a gap of nine days. But there is a switch from a calendar month. So if we use the MONTH formula, the result équal 1 and it is completely false !
As the example has shown, a difference between two dates is not an easy calculation. First of all what kind of difference do we want to calculate? A difference on the months, on the days or on the years? To perform these calculations, Excel has an i-n-c-r-e-d-i-b-l-e function to calculate the differences between the dates but this function is hidden 😕
True ! If you open the function dialog box, you will not see the function DATEDIF in the list of Excel functions but this function works perfectly.
The DATEDIF function
This function requires three parameters:
- a date
- a second date (necessarily greater than the first date)
- a parameter to return the gap.
No difficulties for the first 2 parameters so let's see the last one. The third parameter is really important because it precise if you want to calculate a gap in days, months or years. But also if you want to calculate a number of months inside a year (or a number of days in a month).
The following table presents different values for the third parameter (should always set the third parameter in quotation marks). In this example, you have the different result between the date in C1 and C2 in function of the 3rd parameters.
Now, let's solve the problem. You have a document with birthdays and you want to know the age of each people.
- To calculate the number of years, you will specify the parameter "y".
- To calculate the number of month, in the current year "ym"
- To calculate the number of days in the current month "md"
The formulas in C5, D5 and E5 are
In the following worksheet, calculate for each person the gap in years, months in the current year and days in the month.