Given a person's date-of-birth, calculate the number of months until their next birthday in Excel

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.
WeThotUWasAToadAsked:
Who is Participating?
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.

byundtMechanical EngineerCommented:
I feel certain barry houdini will school me on this one.

I think you can get the number of full months (not rounded) between today and a person's next birthdate using:
=11-MOD(DATEDIF(A2,TODAY()-1,"m"),12)

I get equivalent results in my test cases with:
=11-MOD(DATEDIF(A2+1,TODAY(),"m"),12)
0

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 trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Another way is to calculate the diff in months of thebirthday from a future date, e.g. today()+365, modulo 12.
0
[ fanpages ]IT Services ConsultantCommented:
For your information:

A previous thread (where barryhoudini's suggestion was the accepted as the answer):

"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 ]
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

ProfessorJimJamCommented:
to calculate number of months to the upcoming birthday

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"))+((DATEDIF(A2,B2,"MD"))/(DAY(EOMONTH(B2,0))))
0
Rob HensonFinance AnalystCommented:
ProfJJ - with your formula and the following inputs:

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),MONTH(A4)-MONTH(A6),12-(MONTH(A6)-MONTH(A4)))

DOB in A4
TODAY in A6

Result gives 8

Thanks
Rob H
0
Danny ChildIT ManagerCommented:
In practical terms, you probably need to find the date of their *next* birthday from their DoB.
This does it
=EDATE(A1,12*(DATEDIF(A1,TODAY(),"y")+1))
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
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
With above dates, the correct result is 8.
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)?
0
Rob HensonFinance AnalystCommented:
DanCh99 - your formula with the dates I suggested gives a result of 8 Months and 6 Days.

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.
0
Danny ChildIT ManagerCommented:
Hi Rob,
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
0
Danny ChildIT ManagerCommented:
Ignore last lines above, still wrong on Feb to Jun as well.  Back to the drawing board...
0
barry houdiniCommented:
I feel certain barry houdini will school me on this one
I 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,"ym")

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()-1,"y")*12+12)-EDATE(TODAY(),B2)

regards, barry
0
Rob HensonFinance AnalystCommented:
Maybe WeThotUWasAToad needs to let us know what level of accuracy is required for his
 
correct and exact answer
In the meantime, slightly shorter version just looking at month numbers:

=MONTH(Q2)-MONTH(Q4)+IF(MONTH(Q2)>MONTH(Q4),0,12)

Thanks
Rob
0
Rob HensonFinance AnalystCommented:
Like a dog with a bone, try this:

=MONTH(A2)-MONTH(A4)+IF(MONTH(A2)>MONTH(A4),0,12)-IF(DAY(A2)>DAY(A4),0,1)&" Months "&IF(DAY(A2)>DAY(A4),((A2-EOMONTH(A2,-1))-(EOMONTH(A4,0)-A4))+1,((A2-EOMONTH(A2,-1))+(EOMONTH(A4,0)-A4)))&" Days"

A2 = DOB
A4 = TODAY

Logic:
Month comparison includes day comparison to get whole number of months. Assumes nth day of one month to nth day of following month = 1 whole month.
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),MONTH(A2)=MONTH(A4)),"HAPPY BIRTHDAY!",MONTH(A2)-MONTH(A4)+IF(MONTH(A2)>MONTH(A4),0,12)-IF(DAY(A2)>DAY(A4),0,1)&" Months "&IF(DAY(A2)>DAY(A4),((A2-EOMONTH(A2,-1))-(EOMONTH(A4,0)-A4))+1,((A2-EOMONTH(A2,-1))+(EOMONTH(A4,0)-A4)))&" Days")

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
Nope, still not working quite right!!
0
Rob HensonFinance AnalystCommented:
Think I've now got it:

=IF(AND(DAY(A2)=DAY(A4),MONTH(A2)=MONTH(A4)),"HAPPY BIRTHDAY!",
IF(AND(DAY(A4)<DAY(A2),MONTH(A2)=MONTH(A4)),"",MONTH(A2)-MONTH(A4)+IF(MONTH(A2)>MONTH(A4),0,12)-IF(DAY(A2)>DAY(A4),0,1)&" Months ")
&IF(DAY(A2)>DAY(A4),((A2-EOMONTH(A2,-1))-(A4-EOMONTH(A4,-1))),DAY(A2)+EOMONTH(A4,0)-A4)&" Days")

Thanks
Rob H
0
barry houdiniCommented:
Hello Rob,

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 6 Months & 0 Days but with that last formula I get 5 Months & 30 Days.

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
0
Rob HensonFinance AnalystCommented:
Needed some ">=" rather than just ">".

=IF(AND(DAY(A4)=DAY(A6),MONTH(A4)=MONTH(A6)),"HAPPY BIRTHDAY!",
IF(AND(DAY(A6)<DAY(A4),MONTH(A4)=MONTH(A6)),"",MONTH(A4)-MONTH(A6)+IF(MONTH(A4)>MONTH(A6),0,12)-IF(DAY(A4)>=DAY(A6),0,1)&" Months ")
&IF(DAY(A4)>=DAY(A6),((A4-EOMONTH(A4,-1))-(A6-EOMONTH(A6,-1))),DAY(A4)+EOMONTH(A6,0)-A6)&" Days")

Now gives correct result.

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
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.

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
0
Rob HensonFinance AnalystCommented:
Now with grammatical correctness for single month/day figures and no appearance of Month or Day when 0 for each of those:

=IF(AND(DAY($A$4)=DAY(A6),MONTH($A$4)=MONTH(A6)),"HAPPY BIRTHDAY!",
IF(OR(AND(DAY(A6)<DAY($A$4),MONTH($A$4)=MONTH(A6)),MONTH($A$4)-MONTH(A6)+IF(MONTH($A$4)>MONTH(A6),0,12)-IF(DAY($A$4)>=DAY(A6),0,1)=0),"",MONTH($A$4)-MONTH(A6)+IF(MONTH($A$4)>MONTH(A6),0,12)-IF(DAY($A$4)>=DAY(A6),0,1)&" Month"&IF(MONTH($A$4)-MONTH(A6)+IF(MONTH($A$4)>MONTH(A6),0,12)-IF(DAY($A$4)>=DAY(A6),0,1)>1,"s "," "))
&IF(IF(DAY($A$4)>=DAY(A6),(($A$4-EOMONTH($A$4,-1))-(A6-EOMONTH(A6,-1))),DAY($A$4)+EOMONTH(A6,0)-A6)=0,"",IF(DAY($A$4)>=DAY(A6),(($A$4-EOMONTH($A$4,-1))-(A6-EOMONTH(A6,-1))),DAY($A$4)+EOMONTH(A6,0)-A6)&" Day"&IF(IF(DAY($A$4)>=DAY(A6),(($A$4-EOMONTH($A$4,-1))-(A6-EOMONTH(A6,-1))),DAY($A$4)+EOMONTH(A6,0)-A6)>1,"s","")))

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
0
[ fanpages ]IT Services ConsultantCommented:
Rob:
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

When calculating date intervals, DATEDIF uses the year of Date1, not Date2 when calculating the yd, ym and md intervals.

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.
---
0
Rob HensonFinance AnalystCommented:
Thereby confirming the avoidance of using DATEDIF
0
[ fanpages ]IT Services ConsultantCommented:
Hence, why I posted the previous discussion so early in this thread; to avoid going 'round in circles.
0
barry houdiniCommented:
I wouldn't avoid DATEDIF altogether - the "y", "m" and "d" options work perfectly well AFAIK, if you go back to Brad's original suggestion, i.e.

=11-MOD(DATEDIF(A2,TODAY()-1,"m"),12)

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
0
barry houdiniCommented:
BTW Chip's example doesn't stand up to much scrutiny

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
0
Rob HensonFinance AnalystCommented:
Hi 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
0
WeThotUWasAToadAuthor Commented:
I'm still here, and enjoying this interesting and insightful dialog. Many thanks to all for the great posts.

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
0
WeThotUWasAToadAuthor Commented:
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.
0
WeThotUWasAToadAuthor Commented:
Is it legal to amend a previous post?

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()),MONTH(A2)-MONTH(TODAY()),
IF(MONTH(A2)=MONTH(TODAY()),0,
IF(MONTH(A2)<MONTH(TODAY()),(12-MONTH(TODAY()))+MONTH(A2))))

Thanks again for all the time spent on this.
0
byundtMechanical EngineerCommented:
In questions like this, the Asker gets to define what are the desired results to particular sets of inputs. It is then up to the Experts to devise a formula that produces those results--or else argue for why certain results ought to be different.

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)-MONTH(TODAY()))+2,
12-MONTH(TODAY())+MONTH(A2),
IF(DAY(A2)<DAY(TODAY()),11,0),
MONTH(A2)-MONTH(TODAY()))
0
Rob HensonFinance AnalystCommented:
Apart from the 0 months part, you have pretty much done the same as me for the Month1 - Month2 scenario.

Thanks
Rob

PS there was a lot of finger counting for checking my results.
0
WeThotUWasAToadAuthor Commented:
the Asker gets to define what are the desired results
I 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.
0
Rob HensonFinance AnalystCommented:
So are you after mathematically correct and exact as per your question or what sounds right???
0
WeThotUWasAToadAuthor Commented:
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 whatever I was after when I opened the thread.
0
WeThotUWasAToadAuthor Commented:
Thanks for all the great responses. To do it justice, I really needed about 4000 points to cover everyone and assign what was deserved in this thread but such are the limitations of EE.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.