Solved

Days Until Next Birthday

Posted on 2011-02-11
11
1,076 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now