How do I calculate months remaining till next birthday in Numbers?

Question

How do I calculate months remaining till next birthday in Numbers?

I have a spreadsheet in numbers 08 that I use to track the awards that the boys earn in my Boy Scout troop. I have one column for their name, one column for their birthday, and one column for their age. I want to add a column calculating how many months remaining until they turn 14. I would like to find a formula that calculates this based upon the column showing their birthday. I have tried multiple variations of the datedif and edate functions but they always return some type of error. I would appreciate any help coming up with the correct formula to make this happen.

MacBook Pro, OS X Mountain Lion (10.8.5)

Posted on Dec 29, 2013 5:50 PM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,383 points

Dec 29, 2013 6:28 PM in response to DontTreadOnMe

Hi DTOM,

I use DATEDIF and TODAY functions in Numbers 09 to calculate age. Perhaps you could use this approach to calculate forwards to the next birthday.

=IF(D3="","",DATEDIF(D3,TODAY(),"Y")&"y"&DATEDIF(D3,TODAY(),"YM")&"m")

If a cell in Column D is blank, insert blank (NULL, "") else calculate age in years and months.

May not "click over" until the end of the month.

Also, there is an Excel spreadsheet called Troop Manager

http://www.scouts.sa.scouts.com.au/troopmanagersoftware

It opens in Numbers 09. I don't know about Numbers 08. It might give you some ideas to incorporate into your own Numbers document. For example, who will be the right age to attend the next Jamboree.

Regards,

Ian.

Reply

Answer 2

Jan 1, 2014 9:05 PM in response to Yellowbox

Thanks Ian—that was helpful.

Happy New Year!

Reply

Answer 3

Barry

Level 9

59,861 points

Jan 1, 2014 9:49 PM in response to DontTreadOnMe

Hi D',

Here's a calculation using TODAY and DATEDIF, and the birthdate, plus several date and time functions to construct the date 14 years after the bitrthdate. I've included a column showing the date on which the individual turns 14, but this column is for convenience only, and is not used in the months to 14 calculation in column D.

Formula in column D. Enter in cell D2 and fill down.

D2: =DATEDIF(TODAY(),DATE(YEAR(A)+14,MONTH(A),DAY(A)),"M")

Note that the result is in full months, and does not include the extra days, if any:

Column B uses the DATE() part of the formula to construct and display the date of the Scout's 14th birthday:

B2: =DATE(YEAR(A)+14,MONTH(A),DAY(A))

Regards,

Barry

Reply