Age difference

[Souris]

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

 

[gaftalik]

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

 

[Guest]

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
(e-mail address removed)
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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.