Calculating age on next birthday

[Zaigham]

Hi
I have a table in which I have to calculate the age on next birthday of an employee at the time of his retirement. I can calculate actual age at the time of retirement but unable to calculate his age on next birthday if his date of retirement is a few days greater than his birthday.
Below mentioned table may be helpful to explain my problem.

Excel Workbook
	A	B
1	Example # 1	*
2	Date of Birth	10.01.1970
3	Date of Retirement	09.01.2000
4	Age on retirement	30
5	Age on next birthday	30
6	*	*
7	Example # 2	*
8	Date of Birth	10.01.1970
9	Date of Retirement	10.01.2000
10	Age on retirement	30
11	Age on next birthday	31
Sheet1

 

[BeingYogi]

why not current age and add 1

 

[Zaigham]

why not current age and add 1

Please see the example#2 minutely, if date of retirement is before his birthday than OK otherwise +1. I have made this with a lengthy IF() statement but I was thinking that someone could guide me to a better solution.

 

[AMAS]

Maybe have a look at this similar thread:

http://www.excelforum.com/excel-gen...e-age-of-a-person-on-their-next-birthday.html

AMAS

 

[Zaigham]

Hi AMAS

Thanks for guidance.

 

[barry houdini]

If you are defining age in the way it's generally understood then in your first example isn't the age on retirement still 29 as the 30th birthday hasn't been reached by that date - I agree with BeingYogi, wouldn't age at next birthday always be the retirement age +1?

You can use DATEDIF

=DATEDIF(B2,B3,"y")

and

=DATEDIF(B2,B3,"y")+1

 

[Zaigham]

Hi barry houdini
You are absolutely right 30th birthday hasn't been reached by that date but his age is 30 years =YEAR(B3)-YEAR(B2). From the date of his 30th birthday he will start his 31st year of age. So if his date of retirement falls before his 30th birthday his age will be calculated 30 years. In other case if his date of retirement is equal to or greater than his 30th birthday than we have to tell his age on next birthday i.e., 31st birthday obviously he will be 31 years old on that date.
I have got it with =IF(B3>=DATE(YEAR(B2)+B4,MONTH(B2),DAY(B2)),B4+1,B4)
Any suggestions please.

 

[barry houdini]

I'm not sure I understand that. Is this a specific condition for 30 years or does it apply anywhere?

I don't think that =YEAR(B3)-YEAR(B2) is a particularly good way to calculate age. If B2 is 1st Jan 2000 and B3 is 31st Dec 2001 that calculation will give you 1 for a time gap of 629 days, if B2 is 31st Dec 2004 and B3 is the day after, 1st Jan 2005, then that's a 1 day time gap but that formula will still give you 1

 

[Zaigham]

Hi barry houdini

30 years is just an example it may be 31 or 45 or any age at the time of retirement. However, your suggested "=DATEDIF(B2,B3,"y")+1" is one simple command which I required. It gives me required age i.e., age on birthday falling after the date of retirement. In my actual form I was using a very lengthy formula with multiple sleeves of IF() OR() AND() and DATE() statements.

Thank you very much.

Regards
Zaigham