Date difference in (calendar) weeks, months, years

Started by guuzendesu, September 24, 2018, 01:55:39 AM

Previous topic - Next topic

guuzendesu

It's easy to see how to get a difference in weeks between two dates, but is there a good way to get a difference in weeks, months or years between two dates, with the date as the starting point?

stanl

While I an sure there are easier methods to get what you want with pure WB functions, I have placed some code below which uses WB to call the VBA Datediff() function and return a difference between to dates in weeks. Datediff() permits intervals for days and months and has optional parameters for setting first day of week or year.


Regardless of whether you use it, or a pure WB solution there is likely more setup needed in a script to qualify the intervals.


Code (WINBATCH) Select


#DefineFunction Datediff(d1,d2,interval)
retval=""
oS = CreateObject("MSScriptControl.ScriptControl")
oS.Language = "VBScript"
oS.AllowUI = @FALSE
retval=oS.Eval(: 'DateDiff("%interval%", "%d1%", "%d2%")')
oS = 0
Return(retval)
#EndFunction

d1="4/20/2018"
d2="9/12/2018"
interval="ww"




Message("Weeks from ":d1:" to ":d2,Datediff(d1,d2,interval))


Exit

guuzendesu

Thanks a lot for your detailed and informative answer. But as you guessed, I was looking for something WBT-specific. I had been using a VB6 program, but inexplicably the DateDiff function just now started to be unrecognized. I think it must have something to do with the latest Windows update a couple of days ago, but of course I can't be sure. I added a few lines of code today but everything I added was AFTER the DateDiff function. I changed everything I could, both the target variable and the input dates (to Now-10 and Now) and no matter how simple I made it, the function fails.

No...news flash...

The function only fails on one computer of my two. I'm living in Japan so I suspect a difference in date data, but still I was checking the variables with "IsDate," so i can't comprehend where the error is coming from. One computer is a self-built with a legit and current copy of W10, the other is a Surface Pro 4 likewise updated. So I can't comprehend why the same program fails on one system but not another. My only guess is "well, it's because Windows." With Microsoft, nothing is ever the same twice, even on the same hardware.

td

You can use the TimeDiff function to determine the difference between to dates.  "Easy" is subjective so you may or may not consider the function easy since it requires a small amount of arithmetic for some difference interval types.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

kdmoyers

The mighty Detlev once produced a file FULL of interesting date time functions called udflib.DateTime.wbt.  It does not have that function, but does have WeekOfYear, which is close.  Problem is, I can't find it in the database.  Did I miss it?  Is it kosher for me to post my copy?
-Kirby
The mind is everything; What you think, you become.

td

"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

The following seems to produce the same results as the VBS "DateDiff" functions in limited testing but not entirely convinced that the "DataDiff" function is correct either.
Code (winbatch) Select
;; Weeks between two dates.
#definefunction _TimeDiffWeeks( _Ymdhms1,_Ymdhms2)
   nDays = TimeDiffDays(_Ymdhms2, _Ymdhms1)
   nReturn = nDays/7
   nRemainder = nDays mod 7 
   if nRemainder > 3 then nReturn +=1
   else if nRemainder < -3 then nReturn -= 1
   return nReturn
#endfunction


;; Whole months between two dates.
#definefunction _TimeDiffMonths( _Ymdhms1,_Ymdhms2)
   nMonths = ItemExtract(1,_Ymdhms2,':') - ItemExtract(1,_Ymdhms1,':')
   nMonths *= 12
   nMonths += ItemExtract(2,_Ymdhms2,':') - ItemExtract(2,_Ymdhms1,':')
   return nMonths
#endfunction

;; Whole years between two dates.
#definefunction _TimeDiffYears( _Ymdhms1,_Ymdhms2)
   nYears = ItemExtract(1,_Ymdhms2,':') - ItemExtract(1,_Ymdhms1,':')
   return nYears
#endfunction

d1="10/1/2017"
d2="9/30/2018"

d1=ObjectType('date', d1)
d2=ObjectType('date', d2)

;; Works ???
nWeeks = _TimeDiffWeeks(d1, d2)
nMonths = _TimeDiffMonths(d1, d2)
nYears = _TimeDiffYears(d1, d2)
strOut = $"Weeks %nWeeks%
Months %nMonths%
Years %nYears%
$"
Message('Date Spans', strOut)



Sept 25 - changed the  _TimeDiffWeeks function a bit to handle negative weeks.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade


stanl

Quote from: td on September 24, 2018, 02:50:12 PM
The following seems to produce the same results as the VBS "DateDiff" functions in limited testing but not entirely convinced that the "DataDiff" function is correct either.


If you change the start date to 1/1/2018 - Datediff() can give a different result for weeks depending on if you mess with the optional parameters, like set week start as Monday or year start as first full week.  If you are calculating for a business calendar (with fiscal months or sales vs. support weeks) I suppose those optional parameters would be more relevant.

kdmoyers

Boy, the more you think about date arithmetic, the more edge cases it has.  A fur ball of conditions!
The mind is everything; What you think, you become.

kdmoyers

Quote from: td on September 24, 2018, 11:23:41 AM
Under the "DD Software" bullet:

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+~+WinBatch~Scripting~Resources.txt

Just sifting through that site.... he gave us a lot of great code.
His stuff isn't perfect, but there's a ton of great ideas and huge time savings there.
-Kirby
The mind is everything; What you think, you become.

td

I haven't heard from Detlev for a few years but at least his site is still up and running.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

Quote from: kdmoyers on September 25, 2018, 06:33:14 AM
Boy, the more you think about date arithmetic, the more edge cases it has.  A fur ball of conditions!

If you are referring to Stan's comments, I don't think week start day would affect regular weeks, months, or years calculations.  You would need to adjust the year calculation if you shifted the year to start at the beginning of the week.  I believe there is an example of calculating the day of the week using Julian dates in the Tech Database someplace.  If you want to start the year at the beginning of the week, it would be a matter of shifting the start and end year values before finding the difference.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Tony summed it up when he wrote "Easy" is subjective .....  Basically there are enough date functions in a variety of languages to handle intervals, increments, UNIX conversions, GMT. You name it. 

Of course it is possible to go on a bad date. 


td

There are advantages to being happily married and faithful.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Quote from: td on September 25, 2018, 11:00:40 AM
There are advantages to being happily married and faithful.

Nancy and I have 45 years in that arena.... hmmm wonder how many seconds that is....

Hint: [to the happily part]:  never refer to your spouse as "my wife"