Page History: DateAdd() Function (VB work-a-like)
Compare Page Revisions
Page Revision: 2018/01/02 17:28
The following is a DateAdd function for calculating dates with x intervals added (or subtracted).
It is inspired by the
VB function of the same name but is tweaked slightly from the original. The concept is; return 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
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.
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). International sort format (yyyy-mm-dd) is not currently supported.
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 ut,x,y,z
Local String a$
a$=dt ' dt destroyed by unixtime
ut=UnixTime(a$)
Select Case LCase$(Interval)
case "s"' Seconds
DateAdd=HumanTime(ut+Num)
case "m"' Minutes
DateAdd=HumanTime(ut+(Num*60))
case "h"' Hours
DateAdd=HumanTime(ut+(Num*3600))
case "dd" 'Days
DateAdd=HumanTime(ut+(Num*86400))
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
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