Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Perform case-sensitive Lookup in Excel
  • How to get address of last cell in range in Excel
  • How to use Excel VLOOKUP Function
  • How to use Excel INDIRECT Function
  • Basic INDEX MATCH approximate in Excel

Data Analysis Examples

  • How To Remove Duplicates In Excel Column Or Row?
  • Use Data Form to input, edit and delete records in Excel
  • How to Use Solver Tool in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Excel Pie Chart

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation specific characters only
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in specific year
  • Excel Data validation require unique number

How to calculate next anniversary date or birthday in Excel

by

To calculate the next anniversary date, you can use a formula based on the EDATE and DATEDIF functions.

Case Study: Assume fixing anniversary date as June 1, 2017 in all examples.

Formula

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

Explanation

In the example shown, the formula in D5 is:

 =EDATE(B5,(DATEDIF(B5,C5,"y")+1)*12)

This formula will work to calculate next upcoming birthday as well.

Worked Example:   Get days between dates ignoring years in Excel

How this formula works

Working from the inside out, we use the DATEDIF function to calculate how many complete years are between the the original anniversary date and the “as of” date, where the as of date is any date after the anniversary date:

DATEDIF(B5,C5,"y")

As of today

To calculate the next anniversary as of today, use the TODAY() function for the as of date:

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

Note: Because we are interested in the *next* anniversary date, we add 1 to the DATEDIF result, then multiply by 12 to convert to years to months.

Worked Example:   How to calculate months between dates in Excel

Next, the month value goes into the EDATE function, with the original date from column B. The EDATE function rolls the original date forward by the number of months given in the previous step which creates the next upcoming anniversary date.

Worked Example:   EDATE function: Description, Usage, Syntax, Examples and Explanation

Post navigation

Previous Post:

How to calculate Next working/business day in Excel

Next Post:

List holidays between two dates in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Count Number of Words in Excel
  • Two ways to Compare Text in Excel
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to count total words in a cell in Excel
  • SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation
  • Convert text timestamp into time in Excel
  • Basic overtime calculation formula in Excel
  • How to calculate next day of week in Excel
  • Two ways to sum time over 30 minutes in Excel
  • Get last day of month in Excel
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate original loan amount in Excel
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • Bond valuation example in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles