software

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

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: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=529916tried though getting 29-Aug-2004, which is not correct

please help

thanks

quot;robert111quot; wrote:

gt;
gt; assume cell A1 contains 4 years 8 months
gt;
gt; in B1 put this formula
gt;
gt; =(value(left(A1,1))*365) (value(mid(A1,9,2))*30.4)
gt;
gt; this does not allow for leap years or the fact that months have either
gt; 28, 30 or 31 days.
gt;
gt; If you know that 4 years 8 months zero days is correct on todays date,
gt; you can calculate date of birth and then subtract that from todays date
gt; which gives the exact number of days.
gt;
gt;
gt; --
gt; robert111
gt; ------------------------------------------------------------------------
gt; robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
gt; View this thread: www.excelforum.com/showthread...hreadid=529916
gt;
gt;


Hi Gerald,

Based on Today(), I get 8.8.2001 ...
Playing around with datedif() function :
=DATEDIF(D10,B10,quot;Yquot;) amp; quot; Years, quot; amp; DATEDIF(D10,B10,quot;YMquot;) amp; quot; Months,
quot; amp; DATEDIF(D10,B10,quot;MDquot;) amp; quot; Daysquot;HTH
Cheers
Carim--
Carim
------------------------------------------------------------------------
Carim's Profile: www.excelforum.com/member.php...oamp;userid=33259
View this thread: www.excelforum.com/showthread...hreadid=529916thanks 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

quot;Carimquot; wrote:

gt;
gt; Hi Gerald,
gt;
gt; Based on Today(), I get 8.8.2001 ...
gt; Playing around with datedif() function :
gt; =DATEDIF(D10,B10,quot;Yquot;) amp; quot; Years, quot; amp; DATEDIF(D10,B10,quot;YMquot;) amp; quot; Months,
gt; quot; amp; DATEDIF(D10,B10,quot;MDquot;) amp; quot; Daysquot;
gt;
gt;
gt; HTH
gt; Cheers
gt; Carim
gt;
gt;
gt; --
gt; Carim
gt; ------------------------------------------------------------------------
gt; Carim's Profile: www.excelforum.com/member.php...oamp;userid=33259
gt; View this thread: www.excelforum.com/showthread...hreadid=529916
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=33259
View this thread: www.excelforum.com/showthread...hreadid=529916well, 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

quot;Carimquot; wrote:

gt;
gt; The formula uses dd/mm/yyyy
gt;
gt; With the latest input of 10 years 4 months it produces : 08/12/1995
gt; ...
gt;
gt; HTH
gt; Carim
gt;
gt;
gt; --
gt; Carim
gt; ------------------------------------------------------------------------
gt; Carim's Profile: www.excelforum.com/member.php...oamp;userid=33259
gt; View this thread: www.excelforum.com/showthread...hreadid=529916
gt;
gt;


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,quot;Yquot;)
then to get the year Tools GoalSeek your value say 10 ... changing C1
...

Hope this clarifies ...
Carim--
Carim
------------------------------------------------------------------------
Carim's Profile: www.excelforum.com/member.php...oamp;userid=33259
View this thread: www.excelforum.com/showthread...hreadid=529916
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(quot;
quot;,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:
gt; Sorry for not being clear ...
gt; I do not know how to extract an unknown from the datedif() function,
gt; therefore I am using Tools GoalSeek ...
gt;
gt; Say in cell B1, you type =datedif(c1,2006,quot;Yquot;)
gt; then to get the year Tools GoalSeek your value say 10 ... changing C1
gt; ...
gt;
gt; Hope this clarifies ...
gt; Carim--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=529916
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(quot; quot;,A1)-1)*12-MID(A1,FIND(quot;mquot;,A1)-3,2))

note: EDATE requires analysis toolpak addin--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=529916