Age difference

Hi, I know that there is something possible with my problem but I can't
figure it out. I'm not very good with VBA but I can work it out!

I have in cell A1 the date jan012004 in format 01/01/04. In A4 I have the
birth date sept092000 in format 14/09/00.

I want to have the age. And that A4:xx is still doing this. Is anybody have
a suggestion? Tks


0
souris1 (1)
2/26/2004 3:16:09 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
350 Views

Similar Articles

[PageSpeed] 20

Hi,
If you need it in VBA, i  am sorry i cant help if you need a formul
use the datedif
=DATEDIF(Date1,Date2,Interval) 

where interval could be "m" ;"y" or "d"  month, year or day .

Hope that helps.
gaftali

--
Message posted from http://www.ExcelForum.com

0
2/26/2004 3:38:07 PM
Hi Souris
You can use the DATEDIF function for this

=DATEDIF(B1,A1,"y"

This function may not be documented in your version of Excel.  Check out Chip Pearson's write-up at

http://www.cpearson.com/excel/datedif.ht

Good Luck
Mark Graesse
mark_graesser@yahoo.co
Boston MA    
     ----- Souris wrote: ----
    
     Hi, I know that there is something possible with my problem but I can'
     figure it out. I'm not very good with VBA but I can work it out
    
     I have in cell A1 the date jan012004 in format 01/01/04. In A4 I have th
     birth date sept092000 in format 14/09/00
    
     I want to have the age. And that A4:xx is still doing this. Is anybody hav
     a suggestion? Tk
    
    
     
0
anonymous (74722)
2/26/2004 3:41:06 PM
Hi Souris!

Here's a selection of age formulas:
It's an old problem and can be answered in different way. Here�s a
summary of tried and tested formulas:



In all cases I use:

A1

23-Feb-1947

B1

2-Feb-2003



Rather than B1 you might substitute TODAY(). But note that TODAY() is
volatile and recalculates each time the worksheet recalculates. If you
want to �fix� on today�s date enter the date manually or use the
keyboard shortcut Ctrl + ;



Age in completed years:

=DATEDIF(A1,B1,"y")

returns 55



Age in completed months:

=DATEDIF(A1,B1,"m")

returns 671



Age in completed days:

=DATEDIF(A1,B1,"d")
returns 20433

OR

=B1-A1

returns 20433



Age in years and completed months:

=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m"

returns 55 y 11 m



Age in years and days:

=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"

returns 55 y 344 d

(Note: DATEDIF approach using �yd� produces errors)



Age in years, weeks, and days:

=DATEDIF(A1,B1,"y") & " y " &
INT((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/7) & " w
" & MOD((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))),7) &
" d"

returns: 55 y 49 w 1 d

(Note: DATEDIF approach using �yd� produces errors)



Age in years and fractions of a year:

=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEA
R(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))

returns: 55.94246575

(Note: YEARFRAC produces errors where dates are 1 or more years
apart).



Age in years, months and days:

=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m " &
DATEDIF(A1,B1,"md") & " d"

returns: 55 y 11 m 10 d

(But note that this will produce some strange responses and sequence
interpretation difficulties due to the lack of a consistent definition
of a month).



For more on the mysterious DATEDIF function and age generally see:

Chip Pearson:
http://www.cpearson.com/excel/datedif.htm#Age
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Souris" <souris@hotmail.com> wrote in message
news:en7C4tH$DHA.2808@TK2MSFTNGP10.phx.gbl...
> Hi, I know that there is something possible with my problem but I
can't
> figure it out. I'm not very good with VBA but I can work it out!
>
> I have in cell A1 the date jan012004 in format 01/01/04. In A4 I
have the
> birth date sept092000 in format 14/09/00.
>
> I want to have the age. And that A4:xx is still doing this. Is
anybody have
> a suggestion? Tks
>
>


0
njharker (1646)
2/26/2004 11:58:32 PM
Reply:

Similar Artilces: