I only recently found out about the EDATE() function in Excel and I love it. I wish I’d known about it long before now. It’s nice and simple to create with lots of Use Cases that I can think of.
Note: EDATE has nothing to do with online dating!
EDATE will add (or reduce) a number of months to the selected date.
For example =EDATE(A1+3) will add 3 months to the Date presented in cell A1. It’s that easy – see the image below.
Regardless of which date you use in the cell the correct number of months is added (or removed by using a negative number)
02/02/2022 + 6 months will be 02/08/022
In the past to try and do this Excel users have typically added an average number of days (30 days) to a date to count the number of months. But as we all know some months have 31, 28 or 39, so the resulting final date may not have the correct day of the month (dd) that we were after.
Note: remember to format the cell in the Date format to make the results look right.
Use Cases
EDATE is a simple function but you may not think it’s that useful, that often.
So let me try and highlight some additional use cases for EDATE.
- =EDATE(TODAY(),12) for example allows you to present a date from today. So every time in the Excel workbook is open it will show you the date x months from today.
- =IF(B2=”High”,EDATE(TODAY(),12),”Not High”)
Use EDATE with an IF function. So for example, add 3 months to a Review Date if an Account needs to be reviewed sooner (in 3 months) than an Account that is less important and needs to be reviewed at a later date (in 12 months, for example).
- EDATE can also be used to work backward in time. A negative value will subtract months from your date. For example, take 24 months (or two years) off of a date:
Conclusion
Keep EDATE in mind, I guarantee it will come in handy at some point.
Remember that it adds months to a date without changing the day of the month. If you’re adding months to 23/05/2022 (in the format dd/mm/yyyy), EDATE won’t alter the dd part, only the mm and/or yyyy part, depending on how many months you are adding.
You can also use EDATE to remove the number of months from a date, which is very handy if you have to work backward.
And remember that you can add or remove years from a date by multiplying your years x 12 i.e. 3 years = 36 months.
Give EDATE a go today.
