Datediff & bissextile year

  • Thread starter Thread starter Basta1980
  • Start date Start date
B

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
 
Can you not use:

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

?
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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"))
 
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
 
Rick,

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

Regards,

Basta1980
 
Back
Top