age formulae
 

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

age formulae
 

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 either
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 date
which gives the exact number of days.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

tried though getting 29-Aug-2004, which is not correct

please help

thanks

"robert111" wrote:


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 either
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 date
which gives the exact number of days.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

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
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

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

"Carim" wrote:


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
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

The formula uses dd/mm/yyyy

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

HTH
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

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

"Carim" wrote:


The formula uses dd/mm/yyyy

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

HTH
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

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


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

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

--

Carim Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=529916

age formulae
 

Thanks, Bryan

"Bryan Hessey" wrote:


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

--

Carim Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=529916