# Days Until Next Birthday

Good evening,

I need a formula in Excel that will look at a birth date and then calculate the days until the next birthday. Please see the attached.

Thanks
Birthday-Days-Until-Next.xls
wchh

msis2002,

Try:

=IF(AND(DAY(TODAY())>DAY(C4),MONTH(TODAY())>=MONTH(C4)),DATE(YEAR(TODAY())+1,MONTH(C4),DAY(C4))-TODAY(),DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))-TODAY())

It's in the attached file.

Patrick
Birthday-Days-Until-Next-01.xls
barry houdini

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=IF(DATE(YEAR(B\$1),MONTH(C4),DAY(C4))<B\$1,DATE(YEAR(B\$1)+1,MONTH(C4),DAY(C4)),DATE(YEAR(B\$1),MONTH(C4),DAY(C4)))-B\$1

That's alot of text. I'd never heard of EDATE or DATEDIF - they appear undocumented at least in Excel 2007.

Dave
Hello Dave,

That's essentially the formula that wchh suggested except you shortened it marginally by placing the -B\$1 outside the IF so it's only required once.

You should find EDATE in the help files for Excel 2007. It simply adds n months to a date, i.e.

=EDATE(date,n)

DATEDIF, however, is only in the help files for Excel 2000 apparently (and doesn't appear in the function list either), although available as a built infunction all versions since 95, see Chip Pearson's write-up here

regards, barry
Dave,

>I'd never heard of EDATE or DATEDIF

Mmm, there's even a help file for EDATE() - see below.

Patrick
MWSnap-017-2011-02-13--11-09-39.jpg
ps ...and that help file exists even for Excel2002!
msis2002,

For a measly 50 points this question is barely worth getting out of bed for!

As a Premium Service Member you may ask as many questions you like and offer up to 500 points for any of them.

Patrick
I fumbled that comment.  I had heard of EDATE and used it occassionally.  it was the DATEIF I had lost or forgotten and it wasn't in the help... lol.  Lots of undocumented "features" that are good to catch.  Great that we could show several ways of doing the same thing.

I guess I never used EDATE as it was so easy to remember the DATE function, lol.

That's what I get for putting down the manual after 1997, lol.  Well, I've been catching up alot since then!

Dave

Hello Patrickab,

Thank you for your insight into my being a Premium member.  Unfortunately I take your comment about my submission being "a measly 50 points" and "barely worth getting out of bed for" a bit disturbing and somewhat insulting.  I am not an expert on all the ins and outs of this service, especially the ability to "earn points".  However, I have found it useful and time saving when searching for answers to my questions. I thought that setting the point value on my questions was for the degree of difficulty.  I didn’t think that the answer to my question would be very difficult for people like yourself, hence the 50 points.

I am merely a consumer who up to this point was quite satisfied with the services of expert-exchange. I have been a member for many years. Although I am but one of the possibly thousands of people who use this service I am considering releasing my arrangement and looking for one where my menial requests are answered without ridicule. I looked at your moniker and see that you are ranked as a “Genius”.  I commend you for your knowledge and expertise.

Again, I thank you for your candid response. I wish you and expert-exchange the greatest success today and in the future.

Peace and Blessings
msis2002,

As you have been a member of EE for about 7 years, I would have expected you to be fully aware that everyone here is a voluntary member. We do not provide a service, we answer questions if we are inclined to do so. We are not under any obligation to answer a question. We do not get paid in any way. We only earn points if a question is correct and a questioner awards us some points.

You are fully intitled to offer only 50 points for this question or indeed any other question. However you must surely be aware that a higher number of points on offer attract more people and usually get you an answer that much faster. The choice is yours and it is up to you as to how you want to encourage potential answerers with the points that you offer. If for a moment you look at the open questions in this zone you will see very few 50 point questions. The choice is clearly yours - as it is indeed mine as to whether I attempt to answer this question.

Patrick