Datediff & bissextile year
 

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

Datediff & bissextile year
 

Can you not use:

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

?

"Basta1980" wrote:

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

Datediff & bissextile year
 

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

"Basta1980" wrote:

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

Datediff & bissextile year
 

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
---------------
Jacob Skaria


"Basta1980" wrote:

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

Datediff & bissextile year
 

Hahaha,

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

"Sam Wilson" wrote:

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

"Basta1980" wrote:

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

Datediff & bissextile year
 

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

Datediff & bissextile year
 

Sam,

I tried but it doens't work.

"Sam Wilson" wrote:

Can you not use:

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

?

"Basta1980" wrote:

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

Datediff & bissextile year
 

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Basta1980" wrote:

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

Datediff & bissextile year
 

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

Datediff & bissextile year
 

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(2 000,2,15)) returns13

but

datediff("yyyy",dateserial(1987,2,15),dateserial(2 000,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



"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"Basta1980" wrote:

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

Datediff & bissextile year
 

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

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

Datediff & bissextile year
 

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

If this post helps click Yes
---------------
Jacob Skaria


"Basta1980" wrote:

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Basta1980" wrote:

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

Datediff & bissextile year
 

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


--
Rick (MVP - Excel)


"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

Datediff & bissextile year
 

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 (MVP - Excel)


"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

Datediff & bissextile year
 

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 Rothstein" wrote:

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 (MVP - Excel)


"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

Datediff & bissextile year
 

Rick,

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

Regards,

Basta1980

"Rick Rothstein" wrote:

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 (MVP - Excel)


"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