Work out day's until birthday

Have just been playing around with a few thing's, working out age from TODAY - DOB and stuff, and thought I'd work out how many day's it was until a birthday...

I'm not too sure how to do for some reason, I would have thought that TODAY-(DOB + age) would have given the answer, but it doesn't seem to be working out for me.

DATEDIF doesn't come up prompted when I start typing the formula in, though it works, which is odd? (I thought all formula's came up?)

OK I'm running into a similar problem, it's giving me the answer - 10105 (The DOB is 18/02/1986)

Which is the serial number - but I'm not sure how to convert this to day's rather than years, /365.25 gives me 27/01/1900 which converted to general gives 27.665982 - which is the age rather than the amount of day's until the next birthday...

Not sure where I'm slipping here, thanks though

I presume you want the number of days from today to the next birthday. If so, you need to take the date of birth and use either today's year or next year, depending on when the birthdate actually falls.

Hope this helps.

Pete

Hi, OK I've figured it out a bit of a different way -

=(B4+((C4+1)*365.25))-$D$1

B4 = DOB

C4 = Age

D1 = Todays date


Just had a look at your formula - it still (for me) gives the current age (formatted nicely!) rather than the amount of day's until the next birthday.

Originally Posted by ratcat

G'day

Just putting my 2 cents worth

Have you tried the forumla below. Cell A2 has your next birthday date.

=A2-TODAY()

This is my way of finding the your age. Cell A1 has you birthday date.

=YEAR(TODAY())-YEAR(A1)

Hello Ratcat - I've never really used the YEAR function before.

That's better than the method I used as I had the current date In a cell reference rather than within the formula.

Thanks!

Originally Posted by popipipo

take a look at these solutions

I'm looking at this solution at the moment -

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

And just getting my head around it, not too sure what's going on with it...

IF=(DATE(YEAR(TODAY))),

This section - this is defining the criteria for the if function?

MONTH(A1),DAY(A1))>=TODAY(),
=
So this is the value if true? I don't get what this is doing, the month and the day of A1 equal to the month and day of TODAY.... ?

DATE(YEAR(TODAY())

Again I don't get this...


Basically I really can't see the individual parts that are going on with this formula :/

Cheers

Originally Posted by Managerwork

I'm looking at this solution at the moment ...

Similar to mine in Post #15, but mine has an implied IF.

If you look at Post #5 you will see what it is doing. Perhaps you are getting confused with all the brackets, as TODAY() has its own pair, so the comparison is:

=IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) >= TODAY(),

or ... IF(adjusted_date >= today

where adjusted_date is the date of birth with the year from today. Now, if that adjusted_date is greater than today then you can just subtract today from it to get the number of days till the next birthday. But if it is less than today (i.e. the DOB occurred in an earlier part of the year) then you must add 1 year onto the adjusted_date before subtracting today.

An example - it is mid October now, but if someone had a date of birth in June then their birthday this year would already have taken place, so their next birthday will occur next year.

Hope this helps.

Pete

Originally Posted by Pete_UK

Similar to mine in Post #15, but mine has an implied IF.

If you look at Post #5 you will see what it is doing. Perhaps you are getting confused with all the brackets, as TODAY() has its own pair, so the comparison is:

=IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) >= TODAY(),

or ... IF(adjusted_date >= today

where adjusted_date is the date of birth with the year from today. Now, if that adjusted_date is greater than today then you can just subtract today from it to get the number of days till the next birthday. But if it is less than today (i.e. the DOB occurred in an earlier part of the year) then you must add 1 year onto the adjusted_date before subtracting today.

An example - it is mid October now, but if someone had a date of birth in June then their birthday this year would already have taken place, so their next birthday will occur next year.

Hope this helps.

Pete

Hi Pete - If I can just break it down a little bit that'd be ace, so the
starting part with the IF Function;


=IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) >= TODAY(),

I can really see what parts of that are the logical test, and what are the true / false responses.

(DATE(YEAR(TODAY())

is that the Logical test? Is that just determining whether it's today's date?

Sorry about this, appreciate the help though.

No, the DATE function takes three arguments - the year, the month and the day, so you are comparing a modified date given by:

DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))

with today's date. I tried to show this with the emboldened black in Post #26. So we are producing a modified date by taking the day of the DOB and the month of the DOB but using the YEAR from today's date, and then comparing that with today's date, so the logical test is:

DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) >= TODAY()

The action-if-true is then:

DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-TODAY()

which means take that adjusted date and subtract today's date from it (as the modified date is in the future, as would occur with a November date-of-birth).

The action-if-false is given by the remaining part of the formula, i.e.:

DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY())

which adds a further 1 onto the year part because the modified date is in the past (like a June date-of-birth).

The formula that I gave works on the same basis, but is a bit more succinct.

Hope this helps.

Pete

Originally Posted by newdoverman

I'll throw in my 2 cents worth:

With the next Birthday in A1 enter this where you what the answer:

Formula:

=IF(A1="","",DATEDIF(TODAY(),A1,"Y")&"  years, "&DATEDIF(TODAY(),A1,"ym")&  " months, and  "&DATEDIF(TODAY(),A1,"md") &" days")

I get a "NUM!" error from that formula for some reason...

Got to go out - just wanted to say a thanks for the posts, there's lot's of stuff I wouldn't (couldn't) have thought of here, and the formatting stuff is great. Will go through and test properly tomorrow morning

With date of birth in A2, put this in B2:

=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY()),MONTH(A2),DAY(A2))-TODAY()

examples:

21/10/1973 2
17/10/1973 363

with today being 19/10/2013.

Hope this helps.

Pete

It seems to me from post #1 that it is the date of birth that is known, so this has to be adjusted to the next birthday.

Pete

Originally Posted by Ace_XL

shg's formula works best..an alternative (my 2 cents!)

With DOB in A2

=DATE(YEAR(TODAY())+(TEXT(A2,"mmdd")<TEXT(TODAY(),"mmdd")),MONTH(A2),DAY(A2))-TODAY()

this worked