DateDiff - Years 7

[SBerthold]

What is the fastest/easiest method in VB to accurately determine the number of years between two dates?

I noticed that there seems to be an error in the DateDiff function because if the first date is 12/31/2001 and the second date is 1/1/2002, the datediff return 1 year, even though actual time passed is only 1 day!

Doing a search in this forum on datediff brings up too many results - if someone could please supply me an answer here or point me to a good link in this forum.

 

[johnwm]

On a side note:
It's interesting to note that all these calcs only help us with future dates, not past dates. In the UK and the then English colonies the Julian calendar was still in use until 1752, which means that all date calculations referring to dates before that are out by 11 days!
Most of the rest of continental europe had changed over to Gregorian calendar by 1587.
For more on this (non-IT) background try:

http://serendipity.magnet.ch/hermetic/cal_stud/cal_art.htm

Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[CCLINT]

CajunCenturion/Schweiger:
Put me out to pasture..I knew that!. I do not know what I was thinking here...Somehow I got on the wrong tract.
(But regardless, Int or \ will not work.)

The figure needs to be rounded:
?CInt((DateDiff("d", date1, date2) / 365.2425))

or better:
?CLng((DateDiff("d", date1, date2) / 365.2425))

Schweiger, now the calculations are the same. So, unless you prove me wrong again, then that should be correct and fastest.


johnwm : also an interesting point, but the functions use the Gregorian calendar. You have to stick to one or the other. And there is the possibilty to change it.

CajunCenturion: I still don't get it with the leap years. Did I miss something in school, or has it been too long?




[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[TimmyNF]

Hi!

This works(date1 and date2 are dates to be compared):

Year(date1) - Year(date2)

If you don't want negative numbers use this:

Abs(Year(date1) - Year(date2))

-Tim

 

[CajunCenturion]

The rules for leap year is as follows:

A leap year is one which is either
1. Divisible by 4 but NOT divisible by 100
or
2. Divisible by 400

The net effect is that the 3 out of ever 4 even hundred years is not a leap year
1600 - Leap year, divisible by 400
1700 - Not a Leap Year
1800 - Not a Leap
1900 - Not a Lepp
2000 - Leap Year - divisible by 400
etc..


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

And as far as being put out to pasture --

Welcome, I'm glad to finally be in some good company. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

TimmyNF: That cannot work. We are looking for whole years that have past.

?Abs(Year(#12/31/2001#) - Year((#01/01/2002#))

produces 1 year....but, only one day has passed.
--------------------------------------------------------

I was out to lunch and check up and found the answer, typed up my post and wanted to post it, but now see that CajunCenturion has already answered it.

But I guess I will still post it:

CajunCenturion:
Now I understand. I check up on the Gregorian Calendar and what you stated that it is based on 400 year cycles. This is correct.
A Leap year is defined as a leap year when the last 2 digits are divisible by 4, AND the century is divisible by 400.
This means that 500, 600, 700, (not 800), 900, 1000, 1100 (not 1200), 1300, 1400, 1500, (not 1600), 1700, 1800, 1900, (not 2000)....are not leap years.

These centeries can not be evenly divided by 400.
Thus, over a 1500 year time span, there are 12 centuries with one less day, or one less leap year.

Thus: 1508 / 4 = 377.
There are 12 centuries with one less day: 377 - 12 = 365.

[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Schweiger]

CCLINT

I think I got the point now. In the 400 years there are 97 leap-years. 97 / 400 = 0.2425. Now I understand the mathematics and you're right. Your code is accurate.
Now let's speed it up...

?Abs(cLng((date1 - date2)/365.2425))

Is supposed to return the same result but is about 25 times faster.

Andreas

 

[CCLINT]

Schweiger: [smiling] Again I was just about to post the same. Super![smiling]

Please note: data1 and Date2 must be date variables, otherwise CDate() will need to be used.

Shoot! you beat me too it!

Anyways, thanks for your persitence and keen eye! With-out it we would haven't come to this solution and I would not have an accurate answer)I'll give you a star, and SammyB should give us a response.

So here are the 3 best solutions so far:

1. ?CLng(Date1 - Date2 / 365.2425)
Which is now the fastest.

2. ?DateDiff(&quot;yyyy&quot;, dateStart, dateEnd) + (DateSerial(Year(dateStart), Month(dateEnd), Day(dateEnd)) < dateStart)

This is the 2nd fastest of the 3 functions (twice as slow as #1).
But only works in a certain order. The start date has to be less than the end date, otherwise, you need to check for this, and if it isn't, flip the values in the variables around. That will make it a little slower yet.

3. ?(Format(dateStart, &quot;yyyymmdd&quot - Format(dateEnd, &quot;yyyymmdd&quot) \ 10000

Please note that I removed from the above code the CLng functions (not needed) and change the divider from &quot;\&quot; to &quot;/&quot;, and removed the Abs function (if the start date > End date, then it should be a minus. If the user wants a plus, then they should use the Abs function on the result.

This 3rd example is now about 3 times slower than #1.


[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Schweiger]

I've still got many thoughts about the subject...

I've prefered the format-thing because of the following:
- it is a very general approach (as you can use it even in
other languages without the datediff-function)
- it doesn't have to take leap-years into account
- I didn't understand the 365.2425 ;-)
By the way, if you use format$ it's slightly faster...
But it's beating that it's slow. Does anybody know if sql-server supports datediff?

My star to both of you CCLINT and CajunCenturion guiding to the solution and pointing out why it works at all.

Andreas

 

[CajunCenturion]

Schweiger - The Gregorian Calendar is based on a 400 year cycle. It takes a full 400 years to to cycle thru every possible combination of leap years is, that being because only 1 out of every 4 century years is a leap year.

If you multiply it out - that means over the 400 year cycle, there will be 146097 days.
Dividing 146097 days by 400 years results in an average of 365.2425 days per year. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Suraklyn]

Simply put, you guys astound me. --
Jonathan

 

[SBerthold]

Yes, I thank all of you as well.
It was exciting to see so much action and efforts on the subject. This forum is simply the best!
I will be using the simple CLng(Date1 - Date2 / 365.2425)
method. I have tested it under several languages and it works fine. And is fastest. I prefer to have the option to pass a string or a date to the date function, so I use a string in the args and pass it ByVal, and added checks to see if the args really are dates, and, if so, use the CDate function on them. And, I send the EndDate as an optional argument, so if it is not a date and Len(0) then I set it to Date(). Then the user doesn't need to always pass 2 dates if checking the diff. between one date and todays date.

 

[CCLINT]

Correction from:

CLng(Date1 - Date2 / 365.2425)

To:

Int((Date1 - Date2) / 365.2425)

Or, depending on it's use:

Int((Date1 - Date2 +1 ) / 365.2425)


Please also not that Date2 needs to be the oldest date.

 

[strongm]

Always worth throwing another solution into the pot (I'd have been earlier, only I've been ill...):

Private Function vbYearDiff(startdate As Date, enddate As Date) As Long
vbYearDiff = (Year(startdate - enddate) - 1899) '1899 is Year(0)
End Function

 

[CCLINT]

Welcome back and hope you feel better now...

This thread is actually from last year and I only posted a correction as a response to another thread started yesterday I believe.

I've seen this code you've posted before. But you will need to make an adjustment first:

Year(startdate - enddate + 1) - 1899)-1

And, depending on usage, you may have to add 2:

startdate - enddate + 2

And whether the start date is larger than the end date, you may need to drop the -1 at the end:

Or:
Year(startdate - enddate + 1) - 1899) + CBool(startdate > enddate)


Year(1) is also 1899
Year(2) is 1900

 

[strongm]

Arggh. I meant to put this in the other thread. Oh well..

>Year(1) is also 1899

Sure, but that isn't important to the algorithm. What is important is that we are subtracting the year of VB's root starting date (30th December 1899) from our result. The fact that Year(1) also returns 1899 is irrelevant.

>Year(startdate - enddate + 1) - 1899)-1

Well, this goes back to MichaelRed's point about how we are defining the year. Frankly, if I'm born on the 1st Jan 2000 and somebody asked me how old I was on the 1st Jan 2001 I'd like to be able to answer &quot;I am 1 today&quot;. Or, if asked many years ago I was born, I'd like to be able to answer &quot;1 year ago&quot;.

Under those circumstances the function works fine, and order of dates is unimportant; it would correctly generate 1 and -1. It's still slower than yours, though...

 

[CCLINT]

Now, don't take me wrong, and maybe I'm looking in the wrong place, But I say it is still off a day.

>Under those circumstances the function works fine

I wasn't so sure about that, and therefore my first response.

Try this:
Todays Date is =#03/18/2003#

startdate=#03/19/1950#
enddate=date
Returns 53
Say tomorrow is my birthday. But today I am already a year older.

Now try this:
enddate=date

startdate=#03/17/1950#
or
startdate=#03/18/1950#
or
startdate=#03/19/1950#

All return the same figures: 53 years


Now, if it is a matter of perspective, then let's do some subtracting based on a 365 day year:

?vbYearDiff(#17/03/2002#,Date) = 1 year
?vbYearDiff(#18/03/2002#,Date) = 1 year
?vbYearDiff(#19/03/2002#,Date) = 1 year

?Date-#17/03/2002# 'returns 366
?Date-#18/03/2002# 'returns 365 = 1 year
?Date-#19/03/2002# 'returns 364

And:

?Csng(Date)-CSng(#17/03/2002#) 'returns 366
?Csng(Date)-CSng(#18/03/2002#) 'returns 365 = 1 year
?Csng(Date)-CSng(#19/03/2002#) 'returns 364

Which one returns the correct year count?

?Csng(Date) = 37698
?CSng(#18/03/2002#) = 37333

37698 - 37333 = 365

And, that's a year.

Maybe just something to keep thinking about.

 

[strongm]

Quite right. Slapped wrists all round in this house...

 

[ADoozer]

i thought id check out johnwm's link but it seems to be down now, however another interesting insight into the calendar is at

http://es.rice.edu/ES/humsoc/Galileo/Things/gregorian_calendar.html

enjoy!! If somethings hard to do, its not worth doing - Homer Simpson

 

[johnwm]

Thanks ADoozer, that looks very like the page I originally referred - it's good to find where it's moved to!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'