Here, we will learn how to use the Excel date functions: DATEDIF, EDATE, EOMONTH and YEARFRAC. Later, we will also learn how to calculate expiry dates using DATEDIF with the IFERROR function.
Excel DATEDIF function
The Excel DATEDIF function returns the difference between the given two dates in years, months or days. DATEDIF takes three arguments: start_date, end_date, unit, all of which are required. The third argument unit will take the time unit as text and must be one of the following: "y": difference in years, "m": difference in months, "d": difference in days, "md": difference in days, ignoring months and years, "ym": difference in months, ignoring days and years or "yd": difference in days, ignoring years.
The formula in D4, copied down, is:
=DATEDIF(A4,B4,C4)
Excel EDATE function
The Excel EDATE function returns the date that is the given number of months before or after the given start date. The formula in I4, copied down, is:
=EDATE(G4,H4)
Excel EOMONTH function
The Excel EOMONTH function returns the last day of the month that is the given number of months before or after the given start date. The formula in M4, copied down, is:
=EOMONTH(K4,L4)
Excel YEARFRAC function
The Excel YEARFRAC function returns the decimal value representing fractional years between the given two dates. The formula in Q4, copied down, is:
=YEARFRAC(O4,P4)
Calculating expiry dates using DATEDIF with IFERROR
To calculate expiry dates, use the formula based on the DATEDIF and the IFERROR functions.
The dataset (shown in the image) contains the data about medicines and their expiry dates. The goal is to calculate the difference between the current date and the expiry date in months using DATEDIF. Furthermore, if the medicine is expired, the formula must return the text "Expired". This is done using the IFERROR function.
The formula in C13, copied down, is:
=IFERROR(DATEDIF(TODAY(),B13,"M")&" months","Expired")
How this formula works
DATEDIF will calculate the difference between the current date (returned by TODAY) and the expiry date in months. In our case, it returns 1. This number is joined with the text string " months", using the ampersand (&) operator. Therefore, we get the result as "1 months". When the current date (returned by TODAY) is greater than the expiry date, DATEDIF will throw the #NUM! error. The error will be trapped by IFERROR that will return the given custom message "Expired".
You have successfully learnt how to use the Excel date functions: TODAY, NOW, DATE, DAY, MONTH and YEAR. You have also learnt how to return the first day of the current year & month and the first day of the current year! I hope this post helped you.
