• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1091
  • Last Modified:

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
0
msis2002
Asked:
msis2002
  • 5
  • 2
  • 2
  • +2
1 Solution
 
wchhCommented:
0
 
patrickabCommented:
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
0
 
barry houdiniCommented:
I don't think that formula works in all cases Patrick, for example if you change C4 to 31/01/1997 you get a negative number as the result.

If you use today's date in B1 and EDATE function then you can use this shorter formula

=EDATE(C4,DATEDIF(C4,B$1-1,"y")*12+12)-B$1

EDATE function is a built in function in Excel 2007 and later - in earlier versions you may have to enable Analysis ToolPak for it to work. To do that use Tools > add-ins and tick the "Analysis ToolPak" box.

If you don't want to use EDATE then this is equivalent:

=DATE(YEAR(C4)+DATEDIF(C4,B$1-1,"y")+1,MONTH(C4),DAY(C4))-B$1

In both cases format result cell as general

Like wchh's suggestion these will both show zero if today is actually the birthday, if you want to display the number of days until next year's birthday on the birthday itself you can just remove the -1 in both of my suggestions.

See attached which has some randomly generated birthdays and a comparison of the results of both of those formulas and wchh's. Press F9 to generate new random birthdates.

regards, barry
26816654.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dlmilleCommented:
=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
0
 
barry houdiniCommented:
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
0
 
patrickabCommented:
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
0
 
patrickabCommented:
ps ...and that help file exists even for Excel2002!
0
 
patrickabCommented:
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
0
 
dlmilleCommented:
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
0
 
msis2002Author Commented:
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
0
 
patrickabCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now