Solved

Days Until Next Birthday

Posted on 2011-02-11
11
1,082 Views
Last Modified: 2012-05-11
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
Comment
Question by:msis2002
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 8

Expert Comment

by:wchh
ID: 34876827
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34878009
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 50 total points
ID: 34878187
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 41

Expert Comment

by:dlmille
ID: 34881056
=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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34882251
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34882295
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34882301
ps ...and that help file exists even for Excel2002!
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34883231
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34883606
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
 

Author Comment

by:msis2002
ID: 34884371
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34884428
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question