Page History: DateDiff() Function (VB work-a-like)
Compare Page Revisions
Page Revision: 2018/01/04 16:38
The following is a DateDiff function for calculating the difference of intervals between two dates.
It is inspired by the VB function of the same name but is tweaked slightly from the original. The concept is; return an integer of the number of specified intervals between the two datetimes. Intervals are specified with a string and are:
yyyy - Year
mm - Calendar Month
w - Calendar week (7 days)
dd - Day
h - Hour
m - Minute
s - Second
If the first date is earlier than the second, the result will be positive otherwise negative.
There is little to no error checking so if you supply nonsense you will probably crash it or get rubbish back.
Syntax:
=DateDiff(interval,datestr1,datestr2)
Examples:
Print DateDiff("mm","01-01-1971 00:00:00","01-06-1980 00:00:00") ' show the number of whole months between the dates
InvoiceDays=DateDiff("d",InvDate$,Now()) ' calculate the number of days since the invoice was raised
Print DateDiff("yyyy","28-02-2010 17:00:00","01-01-1970 00:00:00")' show the number of years between the two dates
Dependencies:
UnixTime
HumanTime
Code:
Function DateDiff(Interval As String,dt1 As String,dt2 As String) As Integer
'return the number of whole intervals between two dates
'Result is +ve when DT1<DT2
'DateDiff ("dd","01/02/2001",Now()) returns the difference in whole days since 1st Feb 2001
Local Integer n,s,t,u,v,x,y,z
Select Case LCase$(Interval)
Case "s"' Seconds
DateDiff=UnixTime(dt2)-UnixTime(dt1)
Case "m"' Minutes
DateDiff=(UnixTime(dt2)-UnixTime(dt1))\60
Case "h"' Hours
DateDiff=(UnixTime(dt2)-UnixTime(dt1))\3600
Case "dd" 'Days
DateDiff=(UnixTime(dt2)-UnixTime(dt1))\86400
Case "w" 'Weeks
DateDiff=(UnixTime(dt2)-UnixTime(dt1))\604800
Case "mm"' calendar Months
t=UnixTime(dt1):u=UnixTime(dt2):s=Sgn(u-t)
If s Then
dt1=HumanTime(Min(t,u)):dt2=HumanTime(Max(t,u))
t=Val(Left$(dt1,2)):u=Val(Mid$(dt1,4,2)):v=Val(Mid$(dt1,7,4))
x=Val(Left$(dt2,2)):y=Val(Mid$(dt2,4,2)):z=Val(Mid$(dt2,7,4))
for n=v+1 to z
DateDiff=DateDiff+12
next
DateDiff=DateDiff+(y-u)
If t>x then DateDiff=DateDiff-1
EndIf
DateDiff=DateDiff*s
Case "yyyy"' Years
t=UnixTime(dt1):u=UnixTime(dt2):s=Sgn(u-t)
If s Then
dt1=HumanTime(Min(t,u)):dt2=HumanTime(Max(t,u))
t=Val(Left$(dt1,2)):u=Val(Mid$(dt1,4,2)):v=Val(Mid$(dt1,7,4))
x=Val(Left$(dt2,2)):y=Val(Mid$(dt2,4,2)):z=Val(Mid$(dt2,7,4))
DateDiff=z-v
If u>y Then
DateDiff=DateDiff-1
ElseIf u=y Then
If t>x Then DateDiff=DateDiff-1
EndIf
EndIf
DateDiff=DateDiff*s
Case Else
DateDiff=0
End Select
End Function