Datediff & bissextile year
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
"JP Ronse" wrote in message
...
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
"Basta1980" wrote in message
...
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" wrote:
Hi,
Try ...
datediff("yyyy",dateserial(1987,2,15),dateserial(2 000,2,15)) = 13
You have to replace dateserial functions by the cell values.
Wkr,
JP
"Basta1980" wrote in message
...
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
|