How to calculate a person's next age

mkhamli

New Member
Joined
Feb 20, 2002
Messages
3
Hi.
I'm using DATEDIF to calculate a person's age ( DATEDIF(A1,NOW(),"y"). My question is how to calculate a person's age next b'day? The condition is, if the month for the person's b'day have not passed today's month, then the age is current age. If the month have passed today's month, then add 1 to the person's age.
Example:-
Cell A1 : 18-Aug-1967
DATEDIF(A1,NOW(),"y")
Result : 34 years
Age next b'day should be 34 years as the month (Aug) have not yet reached this year. If the month as exceed today's month, then add 1 to age ( i.e 35 years ).

Thanks
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I must be missing something here. Isn't a person's age on their next birthday always 1 year more than their age now, no matter what day it is?
 
Upvote 0
On 2002-02-21 15:26, mkhamli wrote:
Hi.
I'm using DATEDIF to calculate a person's age ( DATEDIF(A1,NOW(),"y"). My question is how to calculate a person's age next b'day? The condition is, if the month for the person's b'day have not passed today's month, then the age is current age. If the month have passed today's month, then add 1 to the person's age.
Example:-
Cell A1 : 18-Aug-1967
DATEDIF(A1,NOW(),"y")
Result : 34 years
Age next b'day should be 34 years as the month (Aug) have not yet reached this year. If the month as exceed today's month, then add 1 to age ( i.e 35 years ).

Thanks

I have to agree with Steve...if you want someone's age on their next birthday, just use

DATEDIF(A1,NOW(),"y")+1

If, on the other hand, you want to know how old they will be THIS (calendar) YEAR, then you could try something like this:

=DATEDIF(A1,NOW(),"Y")+(DATE(YEAR(NOW()),MONTH(A1),DAY(A1))>NOW())

Hope this helps,

Russell
 
Upvote 0
Hi Russel, Steve.
Thanks for the tips. Actually, I wanted to set a condition whereby if the month that the person was born does not exceed the current month ( i.e Feb ), then age next b'day is 34 years. If the month birth for that person exceeds the current month, then age next b'day will be 34 + 1. I hope my explaination is clear.

Thanks.
 
Upvote 0
Hi Russel, Steve.
Just wanted to make things clear.
Example:-
My b'day : 18-Aug-1967
Age as of 2002 ( current ): 34 years
Since the month of August has not been exceeded yet, so my age on the next b'day will be 34 years. If I have exceeded the month of august, then my age ( next birthday ) will be 35 years. I just wanted to make a condition for the above case using the IF statement.

I hope you can understand what I'm trying to say.

Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top