In Excel, calculating dates can sometimes be complex. It may be easy to calculate the number of days between two dates, but it quickly becomes more complex when you want to calculate the number of periods (months, years) between those two dates.
Let me introduce you to the Datedif function
A hidden function in Excel is the Datedif function (read as Date difference; the difference between dates). This function does not appear in the list of functions or in ‘Help’, even though it can be extremely useful for simplifying date calculations.
The syntax is as follows:
=DATEDIF(date1 ;date2 ; interval)
- date1 is the first date of the calculations; the ‘oldest’ date
- date2 is the ‘youngest’ date
- nterval is the period you want to calculate. For interval, you can choose from these options:
- “y” : difference in years
- “m” : difference in months
- “d” : difference in days
- “ym” : difference in months, after deducting years.
- “yd” : difference in days, after deducting years.
- “md” : difference in days, after years and months.