finding next big birthday milestone

[lastnn30]

Hello

I am trying to create a document with formulas that will allow to show when is the next big birthday milestone (ie 40 birthdays - so rounding every 10 years) for clients and so that the excel is able to identify the next milestone and also what would be the calendar date. Please see attached screen shot and the excel file. I would appreciate any help/hint. Thank you very much.

[DJunqueira]

A possible solution:

Please Login or Register  to view this content.

[bsalv]

with the milestones

Please Login or Register  to view this content.

[lastnn30]

with the milestones

Please Login or Register  to view this content.

Thank you all for the help. I really appreciate it. Could you please kindly tell me why you added 0.01 to the formula above? Also, how did you manage to add word "Years" to the number in the Milestones columns. I found that is so amazing and brilliant!

[DJunqueira]

with the milestones

Please Login or Register  to view this content.

I had a cultural problem , nice job.

[bsalv]

0.01 or 1, it's the same, just add a number >0 and <=1.
you calculate the datedifference in years, but when somebody is 40 years and 1 day old, his/her next milestone is 50 years.
So by adding a small number and rounding up to the next decade, the formula choices the right decade.
min 0.01, roundup(40+.01) to next decade =50
Max 1, otherwise at roundup(49+1) to next decade =50

[lastnn30]

0.01 or 1, it's the same, just add a number >0 and <=1.
you calculate the datedifference in years, but when somebody is 40 years and 1 day old, his/her next milestone is 50 years.
So by adding a small number and rounding up to the next decade, the formula choices the right decade.
min 0.01, roundup(40+.01) to next decade =50
Max 1, otherwise at roundup(49+1) to next decade =50

Got it. Thank you so much. One more thing and sorry for bothering you. How did you manage to add word "Year" to the next millstone year number? I tried it myself and it only give me for example 40 but not like your, 40 Years. How did you do that. Thank you alot once again.

[bsalv]

Don't know how you get there in an english version, but I suppose
rightclick >cellformat>number>ajusted(?) and then as type you enter 0 " years"

[lastnn30]

I got it. Thank you very very much indeed.