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()