age formulae

G

Gerald

Hi All,

I have a field which has age as 4 years 8 months, how will I know the age in
dd/mm/yyyy

any help will be appreciated

thanks
 
R

robert111

assume cell A1 contains 4 years 8 months

in B1 put this formula

=(value(left(A1,1))*365)+(value(mid(A1,9,2))*30.4)

this does not allow for leap years or the fact that months have eithe
28, 30 or 31 days.

If you know that 4 years 8 months zero days is correct on todays date
you can calculate date of birth and then subtract that from todays dat
which gives the exact number of days
 
C

Carim

Hi Gerald,

Based on Today(), I get 8.8.2001 ...
Playing around with datedif() function :
=DATEDIF(D10,B10,"Y") & " Years, " & DATEDIF(D10,B10,"YM") & " Months
" & DATEDIF(D10,B10,"MD") & " Days"


HTH
Cheers
Cari
 
G

Gerald

thanks Carim, your formulae will give the age in years and months

I need in dd/mm/yyyy format

e.g. if the age of a person is 10 years 4 months what would be the dob

thanks
 
C

Carim

The formula uses dd/mm/yyyy

With the latest input of 10 years 4 months it produces : 08/12/1995
....

HTH
Carim
 
G

Gerald

well, not very clear

a1 is the field in which I have 8 years and 5 months

below formulae when I enter in b1, does not give me dd/mm/yyyy

thanks
 
C

Carim

Sorry for not being clear ...
I do not know how to extract an unknown from the datedif() function,
therefore I am using Tools GoalSeek ...

Say in cell B1, you type =datedif(c1,2006,"Y")
then to get the year Tools GoalSeek your value say 10 ... changing C1
....

Hope this clarifies ...
Carim
 
B

Bryan Hessey

If A1 contains

4 years 8 months
to
99 years 11 months

then the formula

=TODAY()-(LEFT(A1,2)*365.25)-((MID(A1,7+FIND(
",MID(A1,7,99)),2))*365.25/12)

should give you the -approximate -date past, but note the month i
calculated as 1/12th of a year (not 30 - 31 days etc) and a leap da
error occurs, but since you do not specify days it could be 'clos
enough'.

Hope this helps

--
 
D

daddylonglegs

If you have a text entry in A1 like

10 years 4 months

then this formula will give the date of birth

=EDATE(NOW(),-LEFT(A1,FIND(" ",A1)-1)*12-MID(A1,FIND("m",A1)-3,2))

note: EDATE requires analysis toolpak addin
 
G

Gerald

Thanks, Bryan

Bryan Hessey said:
If A1 contains

4 years 8 months
to
99 years 11 months

then the formula

=TODAY()-(LEFT(A1,2)*365.25)-((MID(A1,7+FIND("
",MID(A1,7,99)),2))*365.25/12)

should give you the -approximate -date past, but note the month is
calculated as 1/12th of a year (not 30 - 31 days etc) and a leap day
error occurs, but since you do not specify days it could be 'close
enough'.

Hope this helps
Click to expand...
 
Top