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

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

The following is a DateAdd function for calculating dates with intervals added (or subtracted).

It is inspired by the VB function of the same name but is tweaked slightly from the original and returns a datetime string (DD-MM-YYYY HH:MM:SS) with x intervals added. 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

By default the interval is added to the supplied date, to subtract it, make the numberof value negative e.g. x=-x or x=x-2*x

There is little to no error checking so if you supply nonsense you will probably crash it or get rubbish back. Dates are intrinsically tied with UnixTime constraints and attempts to work with dates before 01-01-1970 will likely return junk.

Notes: Dates will default to 1st March if it would have resulted in a nonsense leap day. i.e. 29-02-2016 + 1 year is 01-03 (and not 29-02-2017). ISO8601 format (yyyy-mm-dd) is not yet supported. Passing a non-recognized interval will return the empty string "".

Syntax:
=DateAdd(numberof,interval,datestr)

Examples:
Print DateAdd(7,"mm","01-01-1970 00:00:00") ' show the date seven months from 1st january 1970
InvoiceDue$=DateAdd(30,"d",Now()) ' set the due date 30 days from now
Print DateAdd(-85000,"m","28-02-2010 17:00:00")' show the date 85000 minutes before 28th Feb 2010
DSTTime$=DateAdd(60,"m",Now()) ' Calculate +1 daylight saving time

Dependencies:

Code:

   	Function DateAdd(Num As Integer,Interval As String,dt As String) As String
		'return a string of the datetime with the relevant period added
		'add -ve to subtract e.g.
		'DateAdd (2,"mm",Now())  returns the datetime time two months from now.
		'DateAdd(-1000,"dd",Now()) returns the datetime a thousand days ago
		Local Integer x,y,z

		Select Case LCase$(Interval)
			Case "s"' Seconds
				DateAdd=HumanTime(UnixTime(dt)+Num)
			Case "m"' Minutes
				DateAdd=HumanTime(UnixTime(dt)+(Num*60))
			Case "h"' Hours
				DateAdd=HumanTime(UnixTime(dt)+(Num*3600))
			Case "dd" 'Days
				DateAdd=HumanTime(UnixTime(dt+(Num*86400))
			Case "w" 'Weeks
				DateAdd=HumanTime(UnixTime(dt)+(Num*604800))
			Case "mm"' calendar Months
				x=Val(Mid$(dt,4,2))-1: y=Val(Mid$(dt,7,4)): z=Val(Left$(dt,2))
				x=((x+Num) Mod 12)+1: y=y+Num\12
				If (x=2 And z=29) And (Not IsLeapYear(y)) Then ' bludgeon for 29/02 in non-leap year
					x=3:z=1
				EndIf
				DateAdd=ZPad$(z,2)+"-"+ZPad$(Abs(x),2)+"-"+Zpad$(y,4)+ Right$(dt$,9)
			Case "yyyy"' Years
				x=Val(Left$(dt,2)): y=Val(Mid$(dt,4,2)): z=Val(Mid$(dt,7,4)): 
				z=z+num
				If (y=2 And x=29) And (Not IsLeapYear(z)) Then ' bludgeon for 29/02 in non-leap year
					y=3:x=1
				EndIf
				DateAdd=ZPad$(x,2)+"-"+ZPad$(Abs(y),2)+"-"+Zpad$(z,4)+ Right$(dt$,9)
			Case Else
				DateAdd=""
		End Select
	End Function

See Also:
DateDiff()
Now()
DatePart()