age formulae

[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

[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 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.

[Gerald]

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
>
>

[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
Carim

[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

"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
>
>

[Carim]

The formula uses dd/mm/yyyy

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

HTH
Carim

[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

"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
>
>

[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

[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)

in a cell formatted for dd/mm/yyyy 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

Added thought. If your dates in column A represent a child's age at some point in time, like the start of the current sporting or school season, then that date should be used in place of 'today()' to calculate back to the DOB.


--

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

[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

[Gerald]

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
>
>