Datediff & bissextile year

[Basta1980]

Hi,

I have a list of employees in column A2 through to column A*. In Column B2
through to Column B is their corresponding d.o.b. I have a code (used from
other thread in this community) to retrieve age in years. This works fine,
except for years leading upto a bissextile year. What happens is when I have
d.o.b. 15-2-1988 and age per is 15-2-2000 the result is 12 (which is
correct). But when d.o.b. is 15-2-1987 and age per is 15-2-2000 the result is
also 12 (which should be 13). How can I solve this problem?!

The code is

ActiveCell.Offset(0, 1) = Int(DateDiff("d", ActiveCell.Offset(0, 0).Value,
Range("c2").Value) / 365.25)

Regards

Basta1980

 

[Sam Wilson]

Can you not use:

ActiveCell.Offset(0, 1) = DateDiff("y", ActiveCell.Offset(0, 0, Range("c2"))

?

 

[Sam Wilson]

And I'm pretty sure having 12 year old employees is illegal...

 

[Jacob Skaria]

Try
DateDiff("yyyy", Range("A1"),Range("B1"))
OR
ActiveCell.Offset(0, 1) = DateDiff("yyyy", ActiveCell.Offset(0, 0).Value,
Range("c2").Value)


With your code. Dont use Int() Instead try using Round
ActiveCell.Offset(0, 1) = Round(DateDiff("d", ActiveCell.Offset(0, 0).Value,
Range("c2").Value) / 365.25)


If this post helps click Yes

 

[Basta1980]

Hahaha,

That's right. I was just testing and had a few quiet recent dates.

 

[JP Ronse]

Hi,

Try ...

datediff("yyyy",dateserial(1987,2,15),dateserial(2000,2,15)) => 13

You have to replace dateserial functions by the cell values.

Wkr,

JP

 

[Basta1980]

Sam,

I tried but it doens't work.

Can you not use:

ActiveCell.Offset(0, 1) = DateDiff("y", ActiveCell.Offset(0, 0, Range("c2"))

?

 

[Basta1980]

Hi jacob,

I tried this code before. Thing is, it returns a full year. So if d.o.b.
14-2-1980 and the other date is 13-2-2000, the result will be 20 when it
should be 19.

Regards

 

[Basta1980]

JP,

I changed your suggestion to the part of the dates (see below). But I get an
error telling me that the argument is not optional;

ActiveCell.Offset(0, 1) = DateDiff("yyyy", DateSerial(ActiveCell.Offset(0,
0).Value), DateSerial(Range("c2").Value))

Regards,

basta

 

[JP Ronse]

Hi all,

I was thinking the same way but if you really want to calculate the age of
persoon on a given date then this function is not correct for dates
(day/month) before the d.o.b..

datediff("yyyy",dateserial(1987,2,15),dateserial(2000,2,15)) returns13

but

datediff("yyyy",dateserial(1987,2,15),dateserial(2000,1,1)) returns also 13
however the age is still 12.

To be fully correct, you have to include a test on day & month and depending
on the result decrease datediff with 1.

if month_dob < month_given_date then
datediff
else ''' month_dob >= month_given_date
if day_given_date < day_dob then
datediff-1
else
datediff
end if
end if

Wkr,

JP

 

[JP Ronse]

Hi Basta,

I used dateserial to make a quick test, you can skip that function and
replace it by range("xy"), range("zz") is these contains dates.
It should be something like:

DateDiff("yyyy", ActiveCell, Range("c2))

See also my second mail on this. We where all a bit to fast not taking the
birthday in account.
As you remarked yourself, the age is increasing on the birthday, not
somewhere in the year.

Wkr,

JP

 

[JP Ronse]

Hi Basta,

The full code ...

Range A1:B4 contains

JP 24/02/1956
Basta 19/09/1980
Jacob 21/08/2000
Sam 15/10/1950


Sub Age()
Dim intAge As Integer
Dim rngDOB As Range
Dim intCount As Integer
Dim rngCell As Range
Set rngDOB = Range(Cells(1, 2), Cells(1, 2).End(xlDown))

For Each rngCell In rngDOB
rngCell.Offset(0, 2) = DateDiff("yyyy", rngCell, Date)
If Month(rngCell) < Month(Date) Then
rngCell.Offset(0, 1) = DateDiff("yyyy", rngCell, Date)
ElseIf Month(rngCell) > Month(Date) Then
rngCell.Offset(0, 1) = DateDiff("yyyy", rngCell, Date) - 1
Else
If Day(Date) < Day(rngCell) Then
rngCell.Offset(0, 1) = DateDiff("yyyy", rngCell, Date) - 1
Else
rngCell.Offset(0, 1) = DateDiff("yyyy", rngCell, Date)
End If
End If
Next rngCell
End Sub

Gives:

JP 24/02/1956 53 53
Basta 19/09/1980 28 29
Jacob 21/08/2000 8 9
Sam 15/10/1950 58 59


I'm using Date as second date but yoy can replace it by a cell containing a
date.

Wkr,

JP

 

[Jacob Skaria]

Refer
http://msdn.microsoft.com/en-us/library/aa227466(VS.60).aspx

If this post helps click Yes

 

[Rick Rothstein]

I think this code line will do what you want...

 

[Rick Rothstein]

I think this code will do what you want...

ActiveCell.Offset(0, 1).Value = Year(Range("c2").Value) - _
Year(ActiveCell.Value) + _
(Format(Range("c2").Value, "mmdd") < _
Format(ActiveCell.Value, "mmdd"))

 

[Basta1980]

Rick,

Thanks, this code works. Thing is, i built it in the rest of the code now
and when I run the code I get a type mismatch.

Gr.

Basta

 

[Basta1980]

Rick,

Nevermind my last post. Thanks (and offcourse to the rest who helped me)!!

Regards,

Basta1980