Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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

Thanks

* For my own future reference:

Syntax

DATEDIF(start_date,end_dat

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialA previous thread (where

"Calculate a person's age from their date of birth in Excel"

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28142459.html ]

you need to calculate the months precisely, with its decimals

some months have less days than other, so EOmonth function along with other functions can be of great help.

put the DOB in cell A2 and put =today() in Cell B2 and then in C2 put the following formula

=(DATEDIF(A2,B2,"YM"))+((D

DOB - 21/02/1972

TODAY - 15/06/2015

Result - 3.8

Thats not correct for next birthday but does give number of months since last birthday. Subtracting that result from 12 gives the correct result of 8.2; ie July through January = 7 whole months, the remaining 2 weeks of June and 3 weeks of February gives the 1.2

If the user just wants whole months, you can just use the month figures from the two dates:

=IF(MONTH(A4)>MONTH(A6),MO

DOB in A4

TODAY in A6

Result gives 8

Thanks

Rob H

This does it

=EDATE(A1,12*(DATEDIF(A1,T

where A1 = DoB, of course.

Then, to find the duration from today until then, you can use this:

=DATEDIF(TODAY(),C1,"m") & " months " & DATEDIF(TODAY(),C1,"md") & " days"

where c1 = date of next birthday from formula above.

Note, you will get some strange results for those born on a leap **day** - ie 29 Feb 2004, but as this only occurs for 1 in every approx 1400 births, it's too weird to worry about. Deal with it manually.

M--Personal-ee---date-difference-next-bi

But what about DOB on 01-Feb-1972 and Today 30-Jun-2015?

Is the correct result 8 (because 8 month names are passed), or 7 (because the numeric diff is 7.x)?

15 days remaining of June 15

7 whole months - July through January

21 days of February 16

7 months plus 36 days is same as 8 months and 6 days, assuming a month is 30 days.

Imagine this scenario:

Today = 28 Feb 15

DoB = 21 Feb

Next birthday is 21 Feb 16 however your formula says it is 11 months and 24 days away, it should only be 11 months and 21 days.

agreed, the Feb-->Feb dates are throwing my solution out.. working on it. Thinking that the infamous DATEDIF deprecation bug is hitting me!

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28133886.html

- see the Zorvek comment, and also Fanpages

However,

the Feb-->Jun seems OK to me? I get 7m 17d

- cheers, Danny

I feel certain barry houdini will school me on this oneI think it's the other way round, Brad, your formula looks good to me......

....except you could perhaps use "ym" parameter in DATEDIF to avoid using MOD, e.g.

=11-DATEDIF(A2,TODAY()-1,"

If you have DOB in A2, then you can have Brad's formula in B2 to get the number of full months, and then get the remaining days with this version , using an approach similar to Dan's

=EDATE(A2,DATEDIF(A2,TODAY

regards, barry

correct and exact answerIn the meantime, slightly shorter version just looking at month numbers:

=MONTH(Q2)-MONTH(Q4)+IF(MO

Thanks

Rob

=MONTH(A2)-MONTH(A4)+IF(MO

A2 = DOB

A4 = TODAY

Logic:

Month comparison includes day comparison to get whole number of months. Assumes

Day comparison then uses EOMONTH to allow for correct number of days to finish off current month and start of birth month.

In the event that today is actually the birthday:

=IF(AND(DAY(A2)=DAY(A4),MO

Thanks

Rob H

=IF(AND(DAY(A2)=DAY(A4),MO

IF(AND(DAY(A4)<DAY(A2),MON

&IF(DAY(A2)>DAY(A4),((A2-E

Thanks

Rob H

You say

Assumes nth day of one month to nth day of following month = 1 whole month.....but if DOB is 16th December 1970 and today is 16th June 2015 I'd expect the result to be

I think you can always argue about the results, when month lengths are variable, and as you say, we don't have a well defined requirement here, but I'd expect any solution to give exactly 6 months in that scenario.

My proposed solution will give 6 months and zero days

regards, barry

=IF(AND(DAY(A4)=DAY(A6),MO

IF(AND(DAY(A6)<DAY(A4),MON

&IF(DAY(A4)>=DAY(A6),((A4-

Now gives correct result.

Thanks

Rob H

However, although somewhat longer and more convoluted than using DATEDIF, the formula above works for all the scenarios that I have tried, including Leap Year scenarios.

Thanks

Rob H

=IF(AND(DAY($A$4)=DAY(A6),

IF(OR(AND(DAY(A6)<DAY($A$4

&IF(IF(DAY($A$4)>=DAY(A6),

Edited to return to previous A4 and A6 references.

See attached.

Sheet 1 shows various of the options from above with mine in green.

Sheet 2 has a random date (from 1972) and column A has dates of this year, calculations in column B. Change date in A2 to 01/01/16 to check for next year with that being a leap year.

Thanks

Rob H

Birth-dates.xlsx

With some experimenting, it looks like any scenarios where date of birth is February and current date is also a February date later than birthday date, the DATEDIF function gives incorrect values. If the current date happens to be the 29 Feb, eg next year, the DATEDIF function falls over completely and gives #NUM results.

The thread I quoted above (in comment #3; "2015-06-15 at 10:05:40 ID: 40829619") points to Chip Pearson's site, thus:

[ http://www.cpearson.com/excel/datedif.aspx ]

Chip states here:

---

DATEDIF And Leap Years

For example,

=DATEDIF(Date1,Date2,"md")

returns 28 for Date1 = 1-Feb-2007 and Date2 = 1-March-2009. Since Date1 is not a leap year, the date 29-Feb is not counted. But the same formula with Date1 = 1-Feb-2008 returns 29, since Date1 is a leap year and therefore the date 29-Feb is counted.

---

=11-MOD(DATEDIF(A2,TODAY()

I think that will work correctly to give the number of whole months until next birthday.

Some of the other DATEDIF options have or had bugs in some versions of Excel - for the issues you raised, Rob, i.e.

With some experimenting, it looks like any scenarios where date of birth is February and current date is also a February date later than birthday date, the DATEDIF function gives incorrect values. If the current date happens to be the 29 Feb, eg next year, the DATEDIF function falls over completely and gives #NUM results.

I couldn't re-create that. What version of Excel did you use and which DATEDIF formula?

Thanks

regards, barry

returns 28 for Date1 = 1-Feb-2007 and Date2 = 1-March-2009. Since Date1 is not a leap year, the date 29-Feb is not counted. But the same formula with Date1 = 1-Feb-2008 returns 29, since Date1 is a leap year and therefore the date 29-Feb is counted.

If both dates are the 1st of the month (which they are for both examples given) then "md" option, which shows remaining days after the whole months have been removed should return zero - I don't think you'd ever get 28 or 29 in those examples - I tested in Excel 2003, 2007, 2010 and 2013 and I get zero as expected in all versions - I expect the example should be using different dates

regards, barry

I am using Excel 2010 and the file I uploaded had some pf the DATEDIF calculations from other submissions included on it, not necessarily all of them.

Thanks

Rob H

I'll leave the thread open for a while for any additional comments — and because I don't have a clue how to cover all the points that are deserved. :P

Maybe WeThotUWasAToad needs to let us know what level of accuracy is required…I'm not quite sure how to respond other than to say that, regardless of the units (years vs months vs. days), the answer is correct.

I've been thinking more about your "accuracy" comment Rob, because as I think my reply suggests, with my original post containing the following statement:

"I am interested in a formula which gives the correct and exact answer all the time."I couldn't really grasp why you were asking about accuracy or what you were getting at.

However, perhaps what you meant is more related to definitions since referring to the number of months remaining before some particular date or event is ambiguous. For example, one of my family member's birthday is Nov 5th and if someone (today on June 19th) were to ask:

"How many months until so-&-so's birthday?"

I'd most likely — no, most definitely look at my fingers and start counting:

"Let's see, it's now June so — July, August, September, October, November — there's five months until their birthday."

Or if I wanted to avoid being seen counting on my fingers, I might think/say:

"November is month 11 and June is month 6, so it's five months away."

And notwithstanding Brad's great solution (and since I did not define "months" in my OP), who's to say whether the correct answer is four whole months (as Brad's formula gives) or five "named" months as I ascertained from my fingers? (Ah, the exacting English language!)

Therefore, I think I understand your question/comment regarding accuracy now and I apologize for my hastily-posted reply. Here's the amended version:

"Oops, I guess I neglected to define that didn't I. Well, just take your pick. :P"

By the way, here's what I came up with for the figure-counting method:

=IF(MONTH(A2)>MONTH(TODAY(

IF(MONTH(A2)=MONTH(TODAY()

IF(MONTH(A2)<MONTH(TODAY()

Thanks again for all the time spent on this.

So in that spirit, I question whether 0 ought to be the correct answer if the birthday already occurred earlier in the month. Shouldn't it be 11 months in that case?

=CHOOSE(SIGN(MONTH(A2)-MON

12-MONTH(TODAY())+MONTH(A2

IF(DAY(A2)<DAY(TODAY()),11

MONTH(A2)-MONTH(TODAY()))

Thanks

Rob

PS there was a lot of finger counting for checking my results.

the Asker gets to define what are the desired resultsI don't know whether to grin with the power that gives me or scowl due to the responsibility. :)

I question whether 0 ought to be the correct answer if the birthday already occurred earlier in the month. Shouldn't it be 11 months in that case?If today (June 20th) someone were to ask me:

"How many months until your next birthday?"

a) If my birthday was on June 26th, my response would probably be:

"It's coming up next week so not even a full month."

b) If my birthday was on June 16th, my response would probably be:

"It was just this past week so my next one is not for a year (ie 12 months).

So should the 11 in your formula actually be a 12?

Or suppose your birthday was on May 16th and you were asked the same question. How would you respond? (Actually Brad, I'm certain your brain operates at a higher level than most — higher than mine for sure — so you might nail it right off and give the "whole" months answer of 10, which is probably the most correct response). However, I know I would say (without even thinking about it):

"Oh, it's not for another 11 months."?

And I suppose that (without thinking…) is probably why I am almost always the "Asker" in these threads and not the Expert. :)

On a larger scale however, I think we may be flogging a dead parrot (ala John Cleese & Michael Palin) but many thanks again for the input.

So are you after mathematically correct and exact as per your question or what sounds right???It seems to me that both have been addressed and solutions have been posted.

As I mentioned above, I don't think I had really considered the possible interpretations of my OP until thinking a bit about your "accuracy" comment. But I'm definitely covered for

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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