Go Back

How to Calculate the Next Anniversary Date in Excel

Read time: 20 minutes

Figure 1. Next Anniversary Date in Excel.

We are going to make use of a combination of the Excel EDATE and DATEDIF functions in order to determine the next anniversary date between a specified start and end date in days, months, or years.

Generic Formula

=EDATE(date,(DATEDIF(date,as_of,"y")+1)*12)

  • date is the specified date from which Excel begins to count.
  • as_of is the specified date when Excel stops counting (after the anniversary).

The letter ”y” in our modified formula above is for specifying the difference in years.

How to use the Excel EDATE and DATEDIFF Functions

We are now going to demonstrate how we can use the Excel EDATE and DATEDIFF Functions to get the anniversary date starting from a specific date.

This is possible in 3 simple steps.

  1. Label the columns of our worksheet and enter the date specified start and end dates accordingly.

See example illustrated below;

Figure 2. Next Anniversary Date in Excel.

Ensure that we have blank cells in our NEXT ANNIVERSARY DATE column for Excel to return the calculated dates.

  1. In our example above, the formula to enter into cell C2 is shown below;

=EDATE(A2,(DATEDIF(A2,B2,"y")+1)*12)

Figure 3. Next Anniversary Date in Excel.

Excel DATEDIF function will determine the total number of years occurring between the start date (original anniversary date) and the end date (“as of” date),:

In our worksheet example, we have specified 1/1/2017 as our end (as of) date.

  1. Copy the modified formula in cell C2 down into the remaining cells in our NEXT ANNIVERSARY DATE column to achieve the desired results.

Figure 4. Next Anniversary Date in Excel.

Note

  • We have used the letter “y” in our formula for Excel to calculate the difference in years.
  • For Excel to calculate the difference in months, we should use the letter “m”.
  • For Excel to calculate the difference in days, we should use the letter “d”.

Figure 5. Final Result.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm trying to display an anniversary date if a cell value is equal to 1, or a multiple of 5.
Solved by Z. L. in 20 mins
Hi I got some great help this morning creating a formula; unfortunately I just found a scenario in which the formula doesn't work quite right. Basically we have two columns one for move in date and one for rent increase date. Every tenant receives a rent increase on the anniversary of their move in date. The effective date for our report is May 1, so essentially, anyone who moved in Jan 1 to April 30 would have already received a rent increase in this year, 2017, and their next increase would be the anniversary date in 2018. However, anyone with a move in date between May 1 and Dec 31 under our scenario wouldn't have yet received a rent increase and their increase date would need to be their anniversary date in 2017. What we forgot to account for was anyone who moved in in 2017 after May 1. The formula as it is calculates the increase date as a 2017 date because they have anniversary dates after May 1. However, if you moved in in 2017 you wouldn't be getting an increase in 2017, it would need to be for 2018. I hope this makes sense. Following is the formula we received this morning with A2 being the move in date. =IF(MONTH(A2)>4,DATE(2017,MONTH(A2),DAY(A2)),DATE(2018,MONTH(A2),DAY(A2)))
Solved by B. Q. in 21 mins
I need help with an excel formula. We have a column for move in dates. Each tenant receives a rent increase annually on the anniversary of their move in date. Our effective date is May 1, so any tenants who have move in dates Jan 1 to Apr 30 would have already received an increase this year (2017) so their next increase date would be in 2018. Any move in dates May 1 to Dec 31 would still need to be increased this year (2017) so our excel would need to show the next increase date for them as 2017. I hope this makes sense :)
Solved by G. Q. in 21 mins

Leave a Comment

avatar