Go Back

How to Calculate the Next Anniversary Date in Excel

Read time: 20 minutes

You might have some data in Excel that includes dates and need to determine milestones, such as anniversary dates. In this article, you will find out how to get a next anniversary date.

Calculate the next anniversary date in Excel

In order to calculate the next anniversary date after certain data, you will have to get familiar with DATEDIF and EDATE functions. The DATEDIF function returns a number of years between two dates, while EDATE function returns a date in a certain number of months. Also, you will see how to get a next anniversary date from today.

Formula to find anniversary date

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

Explanation of formula

Let’s first look at DATEDIF function. This function returns a number of years between two dates. The parameters of the functions are Date – a date from which we count years, Date_to – a date to which we count years, so the third parameter is “y”, which tells the function that we want to count years between dates.

The EDATE function returns a date in a selected number of months. Parameters of the function are Date – a date for which we want to add a certain number of months. Months – a number of months that we want to add on a selected date.

Example 1

Now we can look at the example of finding a next anniversary date. The formula looks like:

=EDATE(B3,(DATEDIF(B3,C3,"y")+1)*12)

 

 

Figure 1. Next anniversary date from a certain date

 

The date for which we want to find the anniversary is in column B (“Date”). In column C (“Date from”) we put the date from which we want to find the next anniversary date. As you can see, the formula (DATEDIF(B3,C3,y)+1)*12 will be the number of months for function EDATE, while B3 cell will be the start date.

The formula DATEDIF returns number 1 years between 30-Jun-17 and 1-Dec-18. We must add 1 to this number in order to get the next year and multiply by 12 to get the number of total months. So, the result in the cell D3 is 30-Jun-19, which is the first anniversary for the date 30-Jun-17 after 1-Dec-18.

Example 2

In the second example, we will find the next anniversary from today, so we just don’t need the column C (“Date from”). Instead of this (C3), we will put function TODAY() which returns today date into function DATEDIF.

 

 

Figure 2. Next anniversary date from today

As you can see the next anniversary from today for date 30-Jun-17 (B3) is 30-Jun-19 in the cell C3.

 

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

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