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.

## Leave a Comment