Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Sort by Color in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to create dynamic reference table name in Excel
  • Get column name from index in Excel Table
  • Excel Bar Chart

References

  • How to get last column number in range in Excel
  • Count rows that contain specific values in Excel
  • Extract all partial matches in Excel
  • How to get first row number in range in Excel
  • How to get address of named range in Excel

Data Validations

  • Excel Data validation whole percentage only
  • Excel Data validation date in next 30 days
  • How To Create Drop-down List in Excel
  • Data validation must not exist in list
  • Excel Data validation with conditional list

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)
Next-anniversary-date How to calculate next anniversary date or birthday in Excel

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.

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.

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.

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

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

Logical Functions

  • How to use Excel AND Function
  • How to return blank in place of #DIV/0! error in Excel
  • OR function Examples in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel

Date Time

  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Add business days to date in Excel
  • How to show last updated date stamp in Excel
  • Get day from date in Excel
  • How to calculate most recent day of week in Excel

Grouping

  • Running count group by n size in Excel
  • Group times into 3 hour buckets in Excel
  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel

General

  • List worksheet index numbers in Excel
  • How to choose page/paper size in Excel before Printing
  • Excel Ribbon Quick Overview For Beginners
  • Subtotal by color in Excel
  • How to count total columns in range in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning