Age Formula

D

Debbie

I have a formula that determines a child's age by the month. I noticed
that today 9/16 one of our children turned 1 month old but the answer
still says 0. What is wrong with formula.
It would be nice if I had one formula instead of two. THe formula can
calculate age up to 6 weeks, the in months from 2 months up to 35
months, and then 3 years and up. Can anyone help me?

This is the formula I use for the months
=IF(C87="","",ROUNDDOWN(((TODAY()-C87)/365*12),0))''=


This is the formula I use for the 3 year olds and higher.
=IF(ROUNDDOWN(((TODAY()-B9)/365*12),0)=37,ROUNDDOWN(((TODAY()-B9)/
365*12),0),ROUNDDOWN(((TODAY()-B9)/365),0))

It would be nice to have one formula to calculate all scenarios.
 
D

Debbie

No code for weeks.

You can just subtract the birth date from today's date then divide by 7.

A1 = birth date

=(TODAY()-A1)/7

--
Biff
Microsoft Excel MVP






is there a code for weeks? year = "y", month = "m", day = "d". Whatis
weeks.- Hide quoted text -

- Show quoted text -
Click to expand...


So I get that, but I don't understand how I can make the formula
determine my criteria
=(TODAY()-C87)/7
C87 is DOB
I want it to be IF(c87<8 weeks,then formula to calculate weeks, if(and
(c87>7 wks,c87<24 mo),"then formula to calculate months), if(c87>23
months, then formula to calculate years)

I checked out DATEDIF but that does not calculate weeks. Can anyone
please help me with this formula. It needs to calculate daily. The
formula I had, did not calculate daily. Thanks!
 
B

barry houdini

Hello Debbie,

This formula will show the age in weeks up to 62 days, then the age in
month until 2 years....then the age in years, always showing only whole
weeks/months/years passed.

=CHOOSE(MATCH(TODAY()-B9,{0,62,731}),INT((TODAY()-B9)/7)&"
weeks",DATEDIF(B9,TODAY(),"m")&" months",DATEDIF(B9,TODAY(),"y")&"
years")

Note: I used 62 because using a lower figure like 56 would mean you'd
go from showing 7 weeks to 1 month which doesn't seem to make much
sense....

This way the formula will show 8 weeks for 6 days...then 2 months and
so on.....
 
D

Debbie

Hello Debbie,

This formula will show the age in weeks up to 62 days, then the age in
month until 2 years....then the age in years, always showing only whole
weeks/months/years passed.

=CHOOSE(MATCH(TODAY()-B9,{0,62,731}),INT((TODAY()-B9)/7)&"
weeks",DATEDIF(B9,TODAY(),"m")&" months",DATEDIF(B9,TODAY(),"y")&"
years")

Note: I used 62 because using a lower figure like 56 would mean you'd
go from showing 7 weeks to 1 month which doesn't seem to make much
sense....

This way the formula will show 8 weeks for 6 days...then 2 months and
so on.....
Click to expand...

It says the formula has an error. I changed the cell to be c87. This
is what I entered. Do you see anything wrong?

=CHOOSE(MATCH(TODAY()-c87,{0,62,731}),INT((TODAY()-c87)/7)&"
weeks",DATEDIF(C87,TODAY(),"m")&" months",DATEDIF(C87,TODAY(),"y")&"
years")

Thanks!
 
B

barry houdini

That looks OK to me, it works when I paste it back into my worksheet
although it removes spaces where the formula wraps (although tha
shouldn't give you an error). The formula is of this form

=CHOOSE(MATCH(TODAY()-c87,{0,62,731}),1,2,3)

That should return 1 where you expect weeks, 2 for months and 3 fo
years. Replace 1 with

INT((TODAY()-C87)/7)&" weeks"

replace 2 with

DATEDIF(C87,TODAY(),"m")&" months"

replace 3 with

DATEDIF(C87,TODAY(),"y")&" years
 
D

Debbie

That looks OK to me, it works when I paste it back into my worksheet,
although it removes spaces where the formula wraps (although that
shouldn't give you an error). The formula is of this form

=CHOOSE(MATCH(TODAY()-c87,{0,62,731}),1,2,3)

That should return 1 where you expect weeks, 2 for months and 3 for
years. Replace 1 with

INT((TODAY()-C87)/7)&" weeks"

replace 2 with

DATEDIF(C87,TODAY(),"m")&" months"

replace 3 with

DATEDIF(C87,TODAY(),"y")&" years"
Click to expand...

Could it have something to do with the formatting of C87. It is
formatted as *9/17/2009. Also, C87 has this formula in it:

=IF(ISERROR(VLOOKUP($A87,BB!$A$1:$T$5008,3,FALSE)),"",IF(VLOOKUP
($A87,BB!$A$1:$T$5008,3,FALSE)="","",VLOOKUP($A87,BB!$A$1:$T
$5008,3,FALSE)))

which works fine
 
G

Gord Dibben

The formula Barry posted works for me.

As does the one you changed to C87 that you say throws an error.


Gord Dibben MS Excel MVP
 
D

Debbie

That looks OK to me, it works when I paste it back into my worksheet,
although it removes spaces where the formula wraps (although that
shouldn't give you an error). The formula is of this form

=CHOOSE(MATCH(TODAY()-c87,{0,62,731}),1,2,3)

That should return 1 where you expect weeks, 2 for months and 3 for
years. Replace 1 with

INT((TODAY()-C87)/7)&" weeks"

replace 2 with

DATEDIF(C87,TODAY(),"m")&" months"

replace 3 with

DATEDIF(C87,TODAY(),"y")&" years"
Click to expand...

ok, at least it is not an error now, but example:
DOB is 4/5/2009
The result is 109 YEARS
 
D

Debbie

That looks OK to me, it works when I paste it back into my worksheet,
although it removes spaces where the formula wraps (although that
shouldn't give you an error). The formula is of this form

=CHOOSE(MATCH(TODAY()-c87,{0,62,731}),1,2,3)

That should return 1 where you expect weeks, 2 for months and 3 for
years. Replace 1 with

INT((TODAY()-C87)/7)&" weeks"

replace 2 with

DATEDIF(C87,TODAY(),"m")&" months"

replace 3 with

DATEDIF(C87,TODAY(),"y")&" years"
Click to expand...

I believe it must have something to do with my lookup formula because
if I put the formula in the main table it works fine, when I put this
formula in spreadsheet that is using the lookup value for DOB it does
not work. Is there a way I can tweek the lookup formula or should I
just use a lookup value for the age from other sheet.
 
B

barry houdini

If the C87 formula returns a blank then my formula will fail but you ca
fix that by changing to

=IF(C87="","",CHOOSE(MATCH(TODAY()-C87,{0,62,731}),INT((TODAY()-C87)/7)&
weeks",DATEDIF(C87,TODAY(),"m")&" months",DATEDIF(C87,TODAY(),"y")&
years"))

Does that work for you? I assume that the VLOOKUP returns a date.....
 
D

Debbie

If the C87 formula returns a blank then my formula will fail but you can
fix that by changing to

=IF(C87="","",CHOOSE(MATCH(TODAY()-C87,{0,62,731}),INT((TODAY()-C87)/7)&"
weeks",DATEDIF(C87,TODAY(),"m")&" months",DATEDIF(C87,TODAY(),"y")&"
years"))

Does that work for you? I assume that the VLOOKUP returns a date......
Click to expand...

That all works in the first spreadsheet, but does not work with my
lookup formula in second spreadsheet. My lookup value does return the
DOB.
 
D

Debbie

That all works in the first spreadsheet, but does not work with my
lookup formula in second spreadsheet. My lookup value does return the
DOB.
Click to expand...

THis is my lookup formula:

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5008,3,FALSE)),"",IF(VLOOKUP($A7,BB!
$A$1:$S$5008,3,FALSE)="","",VLOOKUP($A7,BB!$A$1:$S$5008,3,FALSE)))

That is the cell that is not calculating properly. In the other sheet,
my DOB is typed in, no formulas and it works great.
Thanks! Any suggestions?
 
D

Debbie

THis is my lookup formula:

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5008,3,FALSE)),"",IF(VLOOKUP($A7,BB!
$A$1:$S$5008,3,FALSE)="","",VLOOKUP($A7,BB!$A$1:$S$5008,3,FALSE)))

That is the cell that is not calculating properly. In the other sheet,
my DOB is typed in, no formulas and it works great.
Thanks!  Any suggestions?- Hide quoted text -

- Show quoted text -Is there a way you can make this formula calculate bythe day. For example DOB is 9/17 it shows 4 weeks great, but it also shows4 weeks from 9/13 up 9/19. Can this formula actually either say 4 weeks 2 days or may 4 weeks from 9/17 to 9/24 only.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If Blank 2
Expanding Date Formula 0
Excel formula calculating age 4
formula to automatically calculate customer age 1
Calculating age from date of birth and ignoring if blank 3
Search closest day in the past from today 2
Excel formula to incrementally increase a value by 1 every 365 day 2
Rolling 12 month 9

Top