Date difference in (calendar) weeks, months, years

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



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

[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

[td]

Under the "DD Software" bullet:

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

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

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

[guuzendesu]

Thanks all for the input.

[stanl]

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!

[kdmoyers]

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

[td]

I haven't heard from Detlev for a few years but at least his site is still up and running.

[td]

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.

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

[stanl]

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"