Hello,

**Given a person's date-of-birth (DOB), what formula in Excel will display the number of months until their next birthday?**

I am familiar with the formula =DATEDIF()* which makes it relatively easy to calculate a person's age from their DOB:

=DATEDIF(A1,TODAY(),"y")

However, because the year may or may not be the same as =YEAR(TODAY()), it's proving a bit tricky to calculate the number of months (or weeks or days) until their next birthday.

By the way, I've seen a few date-related Excel formulas which, for the month value, use the number of days divided by 30 as a shortcut. While I assume such an approach gives the correct answer *most of the time*, I am interested in a formula which gives the *correct and exact answer all the time*.

Thanks

* For my own future reference:

Syntax

DATEDIF(start_date,end_date,unit)

Start_date A date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")).

End_date A date that represents the last, or ending, date of the period.

Unit The type of information that you want returned:

Unit Returns

"Y" The number of complete years in the period.

"M" The number of complete months in the period.

"D" The number of days in the period.

"MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored.

"YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored

"YD" The difference between the days of start_date and end_date. The years of the dates are ignored.

I think you can get the number of full months (not rounded) between today and a person's

nextbirthdate using:=11-MOD(DATEDIF(A2,TODAY()

I get equivalent results in my test cases with:

=11-MOD(DATEDIF(A2+1,TODAY