< Go Back

Next anniversary date

★ 20 minute read

In this article, you will find out how to get a next anniversary date. 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.

Next anniversary date

Formula

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

Explanation

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 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.

 

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar