Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
datediff | Excel Programming | |||
datediff help | Excel Programming | |||
DateDIFF | Excel Programming | |||
Help with datediff vba | Excel Programming | |||
DateDiff problem | Excel Programming |