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.

 

[Schweiger]

You could use the following:
lngYears = DateDiff("ww",dat1,dat2)\52

hope this helps
Andreas

 

[SBerthold]

Schweiger, that doesn't work.




lngYears = DateDiff("ww",#9/25/99#,#9/15/02#)\52

returns 3 years, and it is actually 2 years 11 months and some days.
Needed are whole years.

lngYears = DateDiff("ww",#9/25/99#,#9/14/02#)\52
equals 2 years.

lngYears = DateDiff("ww",#9/25/99#,#9/23/02#)\52
Should equal 2 years, but doesn't

 

[Schweiger]

SammyB
Sorry, you're absolutly right, a year doesn't have exactly 52 weeks at all. It's my mistake.
I've thought of another approach.
You've only got to determine if the Day and month in the second date is less than the ones in the first date. If this is the case, then there is one year less between them than the difference of the dates actual year. And if not then you can take the difference of the actual year.

?IIF(DateSerial(year(date1),month(date2),day(date2)) < date1,year(date2)-year(date1)-1,year(date2)-year(date1))

or

DateDiff(&quot;yyyy&quot;,date1,date2)-IIF(DateSerial(year(date1),month(date2),day(date2)) < date1,1,0)

or even (who cares readability )

?DateDiff(&quot;yyyy&quot;,date1,date2)+(DateSerial(year(date1),month(date2),day(date2)) < date1)

Do you agree with me? The only thing to handle is, what happens, if the dates are the same.
Andreas

 

[SBerthold]

Schweiger,

DateDiff(&quot;yyyy&quot;,date1,date2)+(DateSerial(year(date1),month(date2),day(date2)) < date1)

Ok, even if the dates are the same, that works except for the following:

Not knowing what order the dates are entered, (data from a db), if date1 comes after date2, I get a minus and incorrect result!

Maybe I need to flip the input around if date1 is > than date2?

 

[MichaelRed]

A few thoughts.

To simplistically resolve the negativity: Abs(Expr)

To go back to the beginning, the 'error' is not in datediff, but your use of the documentation (e.g. HELP). &quot;DateDiff&quot; always returns the number of interval BOUNDARIES between the dates.

A SPECIFIC definition of &quot;Year&quot; would be necessary if you need to process dates on an arbitrary basis. There are at least a couple of well used -if not entirely legal- deffinitions found in 'the literature' (e.g. Tek-Tips threads) so some would use 365 days, others 365.25 days. the choice of these (or other units / values) is up to you, but to refer to the documented behaviour (of Ms. functions) or the suggestions posted as 'wrong' without providing your personal definition of the interval is at least as incorrect.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Schweiger]

Yes, for readability-reasons you could do so...
But I've come to another approach thinking about this problem. You could use the following statement and the order of the dates doesn't matter any more:

?abs(clng(format(date1,"yyyymmdd")) - clng(format(date2,"yyyymmdd")))\10000

I think never ever anybody will really be able to know what you're doing exactly , but it's a matter of comments I guess...
Andreas

 

[MichaelRed]

If that is acceptable, it should be simplified:

? Abs(CLng(Format(DtStrt, "yyyy")) - CLng(Format(DtEnd, "yyyy"))) - 1
[code] MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Schweiger]

MichaelRed

The difference is in the results of the simplifyed code:

Startdate Enddate Res your code Res my code
--------- ---------- ------------- -------------
25.09.1999 26.09.2004 4 5
25.09.1999 25.09.2004 4 5
25.09.1999 24.09.2004 4 4
25.09.1999 23.09.2004 4 4

sorry for the format... this shows another problem. In my translated help there are no interval BOUNDARIES pointed out, but you're right, it's not an error because it actually is written as last line, that yyyy will return 1 all the same there is only one day in between. But there's no hint why it is like that. So I think everybody takes a bit an intuitive approach and there you're very right, nobody ever said, that anything would have to be as you suppose at all.
I suppose, that SammyD likes to have a function that returns the number of years that fully elapse in between the two dates given. As you have suggested you could define your own YEAR. So you could calc: abs(fix((date1 - date2)/yourYEAR)). I thought about that too, but it will fail anytime, if you only have enough time, because the number of days deffering is increasing... My approach will not fail because of time I think, but I don't know exactly what will happen on 29th Feb... Could be a problem, but I don't think so...

Andreas

 

[CCLINT]

This should work each way, each time:

?Int(DateDiff(&quot;y&quot;, date1, date2) / 365.25) [/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

Are 103 Years a very long time?

Startdate Enddate Res your code Res my code
--------- ---------- ------------- -------------
25.09.1899 26.09.2004 105 105
25.09.1899 25.09.2004 104 105
25.09.1899 24.09.2004 104 104
25.09.1899 23.09.2004 104 104

I don't know the word in english at all (sorry for my english in general...) but it's a matter of the spare day of 29. Feb. And the more years are in between the bigger gets the gap.

btw your could doesn't handle wrong order of dates does it?

Andreas

 

[CCLINT]

No, it is not a matter of the leap years (29 Feb.).
Otherwise, 25.09.1999 and 25.09.2004 would produce also a problem.

But, thanks for pointing this out.

How many days are really in a year?
365.24219 should be close enough.

?datediff(&quot;y&quot;,&quot;25.09.1899&quot;,&quot;25.09.2004&quot\365.24219

Also, using &quot;\&quot; instead of &quot;/&quot; to shortens the code even more so.

As you see, I have tested this when the date seperator and format is set to something other than US, as in your example (German).

Wrong order of dates? It uses any US format and the format that the system is set in. What is needed more? [/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

?datediff(&quot;y&quot;,&quot;25.09.2000&quot;,&quot;25.09.3508&quot\365.24219
1509

This is because there are 365 leap years (thank you for the english lesson ) in between the years... (I let the PC count them...).

?abs(clng(format(&quot;25.09.2000&quot;,&quot;yyyymmdd&quot) - clng(format(&quot;25.09.3508&quot;,&quot;yyyymmdd&quot))\10000
1508

What's wrong with my suggestion? Is it wrong, bad manner or is there any backdraw?

Andreas

 

[CCLINT]

>>What's wrong with my suggestion? Is it wrong, bad manner or is there any backdraw?

No, nothing wrong with your code.
Except that yours is 50% slower on my system....and the other hand, mine is only accurate with-in a 1508 year time span.

That is the purpose of this forum - finding a method to solve a problem and improving methods to solve problems. We don't want to stand still do we? You didn't stop at your first posts which didn't work, or?
Don't take it wrong...be happy about it. I am happy when I find a better way to do something. And, to find out, you got to try and test.

Anyways, I never critizied your code in the 2 posts that I have made so far, or? Were's the problem?

I only posted code that worked another way!
It works fast and correct for general purposes (1500+ years is a long time!).

It seems to jump a day only after each1508 years, and then for one day only, so in 9800 years it jumped 6 days.

Why do I come up with 377 leap years and using a counter with IsDate(&quot;29.02.&quot; & Year(datStart)) shows 365 leap years?

Why does it jump a day every 1508 years?
I haven't thought on this one yet.

I will have a look at it tomorrow. [/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!

 

[CCLINT]

Besides, as far as the code that you posted before:

DateDiff(&quot;yyyy&quot;,date1,date2)+(DateSerial(year(date1),month(date2),day(date2)) < date1)


This same logic I actually actually use. And it is also found elsewhere in this forum. I only do a check on the dates as SammyB says, and flip them around if Start > End.....

The code that I posted above was something I did in order to just try and find out &quot;that better method&quot;.
And it has probably also already been thought of in the past....

I will look at all 3 and take the best of the pick for myself (I'll give ya a star if I do take yours - but, I am usually looking for speed and do not worry about time spans over 1500 years - even though the speed is a 50% difference, it is probably anyways to minimal of a difference in time to even consider)
[/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!

 

[CajunCenturion]

CCLINT - The source of the 1 day error every 1508 years may be because of a slight error in 365.24219. The Gregorian Calendar is based on 400 year cycles, or an evenly divided by 7 of 146097 days. Dividing 146097 by 400 yields a value of 365.2425. That slight difference in combination with the 1 day every approximately 2500 years of the Gregorian Calendar may, may account for the 1 day jump 1508 years.

Also, although I haven't run the numbers, but could it be that the difference between 377 and 365 leap years is that every the 3 out of every 4 century years is NOT a leap year?

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

 

[Schweiger]

CCLINT
I'm sorry, but I think you got me wrong. I'm really interested in exactly the stuff you talked about just now. Performance is an issue. I didn't think of it at all! And there are some &quot;does and donts&quot;. I'd like to know this, and I think there are donts in it because you were looking for a better solution and that's the cause why I asked.
And it's even interesting for me that you write:
I am usually looking for speed and do not worry about time spans over 1500 years
Because it shows me an attitude I want to know to get the picture how ppl are thinking about the subjects.

Andreas

 

[CCLINT]

Schweiger: I was just going to post a response to CajunCenturion post, when I saw that you posted again.

Don't worry about it.
There are 3 possible answers now and we are just searching for the best.
I think in this case accuracy is best, because the speed of both functions are sufficient, even if one is faster than the other.

I will post below my response to CajunCenturion. Maybe you can open my eyes on where the problem is and how to correct it and what the correct amount (not per MS - they could be wrong also) should be.

All the best!
-------------------------------------------------------


CajunCenturion:
Thank you for your paticipation and help.
That day error(every few centeries a leap year is missing) is what I was thinking.
The question is: Is that correct? ANd, which count, and leap year count is correct? And, what are the other factors involved?

I myself used the wrong factor(mean days in year).
I checked the books and found that the calendar is based on Mean Tropical Solar Years, which equals: 365.2422 days. But even with this factor it still hangs on the year 1508/1509.
Using the factor that you came up with(365.2425) produces the same results.

I calculated my self the number of leap years in 1508 years, and multiplied that by 366, then added that to the number of remaining years * 365 and come up with a factor of: 365.238952389 days/year, with the last 5 digits continuing the same (unrounded), and with 377 leap years.

Now, I haven't as of yet needed to write any programs for Historians, Astonomers or Archaeologists, mainly business programs, so I really haven't needed this type of accuracy in the past. But, someone here may. Therefore, because of that reason, and my own desire for an accurate method, I would like to investigate this further.

Can anyone shead more light on the subject.

1. How many days are there really in say, a 1508 year time span?

2. Why does this code in a loop through the years report back 365 leap years and not 377 leap years in a 1508 year time span?
If IsDate(&quot;29.02.&quot; & Year(date1)) Then
lCount = lCount + 1
End If


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

?datediff(&quot;y&quot;,&quot;25.09.1899&quot;,&quot;25.09.2004&quot\365.24219

Also, using &quot;\&quot; instead of &quot;/&quot; to shortens the code even more so

If you use \ it doesn't matter if you're using 365 or 365.24219. My helps says, that before the devision it will be rounded to byte, integer or long. So it will use 365.

Andreas

 

[CajunCenturion]

THe exact number of leap years over a 1508 year span will vary depending on the actual boundary dates of the span. Simple Example using a 5 year span:

1897 to 1902 - 0 leap years (1900 is NOT leap)
1902 to 1907 - 1 leap year (1904)
1908 to 1913 - 2 leap years (1908 and 1912)

And to make matters even more difficult to be exact:
Although 1908 to 1913 has two leap years,
Jan 1, 1908 to Jan 1, 1913 -- 2 leap days
but (Feb 29, 1908 and Feb 29, 1912)
Apr 1, 1908 to Apr 1, 1913 -- 1 leap day (only Feb 29, 1912)

With respect to the 1508/1509 issue, that boils down to a VERY fundamental difference between the following two statements:

Int(X / Y) and (X \ Y)

In Int(X / Y) - the division occurs, and the integer function is applied to the result.

In X \ Y, X and Y are ROUNDED to integers before the division takes place.

Debug.Print Int(1507.99 / 1) ==> 1507
Debug.Print 1507.99 \ 1 ==> 1508

Debug.Print 1507.5 \ 1 ==> 1508
Debug.Print 1507.49 \ 1 ==> 1507
Debug.Print 1600 \ 1.5 ==> 800

It is not a good idea, under any circumstances to use the Integer Division Operator Unless both operands are already integers. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein