DateDiff() Function (VB work-a-like)

Modified on 2020/09/16 08:11 by CaptainBoing — Categorized as: Dates, Time and Clocks

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; calculate the number of specified intervals between the two datetimes. Only whole intervals are counted and are specified with a string:
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.

Note: The dates are subject to UnixTime constraints (no earlier than 01/01/1970). ISO8601 format is not yet supported.

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:

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

Bonus! Alternative Day of Week (DoW) function
There are a number of Day-of-The-Week (DoW) functions here in this library, but just to flog the idea to death; here is a DoW function using DateDiff.

UT0 was a Thursday, so by calculating the number of days between then and a given date, adjusting for the position of Sunday (assuming Sunday=0) and then MODing by 7 (days in a week), we can derive the DoW for any given datetime - thus:

Print datediff("dd","01-01-1970 00:00:00","18-01-2018 00:00:00") mod 7 ' should be zero coz both dates are Thursdays

'...bump along for sunday adjustment
Print (4+datediff("dd","01-01-1970 00:00:00","01-01-1970 00:00:00")) mod 7 ' test forUT0 = 4 (thursday)

'try with today's date...
Print (4+datediff("dd","01-01-1970 00:00:00",Now())) mod 7' day today

If you are using DateDiff() in your code, this has to be about the simplest solution for DoW(). If not, you are better off looking at other examples here in the library as the overhead for DateDiff() is fairly high and difficult to justify for DoW() alone.

Function DoW(d$) As Integer
  DoW=(4+DateDiff("dd","01-01-1970 00:00:00",d$)) mod 7
End Function

See Also:
DateAdd()
Now()
DatePart()