birthday calculator

**Prium** · 01-26-2009, 05:49 PM

Hi,
I am hopeless at remembering birthdays tbh - so rather than rely on family to remind me, I decided to make a spreadsheet that shows: D.O.B, current age (in years, months, days), and number of days remaining until next birthday.

Please see attached - I can't figure out why the current age calculation is a month out. e.g 'Sebastian' was born on 16 Nov 2008, which makes him 3 months and 11 days old - but '=TODAY()-C16' yields "00 Years 4 Month(s) 12 Days"

Also, 'Leah' has just her birthday - but now where it is supposed to give 'days until next birthday' it gives an error with the formula: '=DATEDIF(TODAY(),EDATE(C4,(YEAR(NOW())-YEAR(C4))*12),"d")'

**daddylonglegs** · 01-26-2009, 05:57 PM

You can get age in years, months and days with this formula in D4

=DATEDIF(C4,TODAY(),"y")&" years "&DATEDIF(C4,TODAY(),"ym")&" months "&DATEDIF(C4,TODAY(),"md")&" days"

and then days to next birthday

=EDATE(C4,DATEDIF(C4,TODAY(),"y")*12+12)-TODAY()

edit: format the cell as general......

**Prium** · 01-26-2009, 07:15 PM

awesome cheers for that!

how does that last formula work...can you please explain it to me in lay terms?

**daddylonglegs** · 01-26-2009, 08:17 PM

EDATE adds a number of months to a date so, as I'm currently 21 years old then to get the date of my next birthday I need to add 22 years to my birthdate, i.e.

=EDATE(Birthdate,Age*12+12)

I can calculate my age with DATEDIF(Birthdate,TODAY(),"y") so that formula becomes

=EDATE(Birthdate,DATEDIF(Birthdate,TODAY(),"y")*12+12)

Now to calculate the number of days from today until that date we can just subtract TODAY() from the above, hence

=EDATE(C4,DATEDIF(C4,TODAY(),"y")*12+12)-TODAY()

where C4 contains birthdate.

If you want you could embellish that formula to contain a message like "Birthday Today!" on the actual birthday, see the version I posted here (which also avoids using EDATE)

**Prium** · 01-28-2009, 05:35 PM

Thanks man - very helpful!

**paulus69** · 10-07-2009, 12:30 PM

I have used these calculators and get the age but I get the error message when using the code to get the number of days to the next birthday. What am I doing wrong?

**raaj.nirola** · 04-05-2011, 02:17 AM

Originally Posted by Prium

Hi,
I am hopeless at remembering birthdays tbh - so rather than rely on family to remind me, I decided to make a spreadsheet that shows: D.O.B, current age (in years, months, days), and number of days remaining until next birthday.

Please see attached - I can't figure out why the current age calculation is a month out. e.g 'Sebastian' was born on 16 Nov 2008, which makes him 3 months and 11 days old - but '=TODAY()-C16' yields "00 Years 4 Month(s) 12 Days"

Also, 'Leah' has just her birthday - but now where it is supposed to give 'days until next birthday' it gives an error with the formula: '=DATEDIF(TODAY(),EDATE(C4,(YEAR(NOW())-YEAR(C4))*12),"d")'

This formula shows 1 month difference