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"))

?

"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
>
>

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

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

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

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" <(E-Mail Removed)> wrote in message
news:C7C51978-63CA-4320-8E92-(E-Mail Removed)...
> 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,

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

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

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(2000,2,15)) => 13
>
> You have to replace dateserial functions by the cell values.
>
> Wkr,
>
> JP
>
>
> "Basta1980" <(E-Mail Removed)> wrote in message
> news:C7C51978-63CA-4320-8E92-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

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



"Jacob Skaria" <(E-Mail Removed)> wrote in message
news:674894CE-CD18-4903-8F09-(E-Mail Removed)...
> 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
>>
>>