Date Variables

Variable

Action or Value

Example

[DATE]

[DATEUTC]

Current Date in server's zone

Current Date in UTC

7/16/2015

[YEAR]

[YEARUTC]

Current Year in server's zone

Current Year in UTC

2015

[MONTH]

[MONTHUTC]

Current Month in server's zone

Current Month in UTC

[MONTHNAME]

[MONTHNAMEUTC]

Current Month Name in server's zone

Current Month Name in UTC

July

[MONTHSTART(num)]

[MONTHEND(num)]

[MONTHSTART(date|num)]

[MONTHEND(date|num)]

Returns first or last date of the month. If num is empty then it uses today's date to find first or last of this month.

num - number of months from now. Use negative numbers to compute back months.

date - If date value is supplied then the computation stars with that date instead of the current date.

[MONTHSTART] = First day of the current month

[MONTHEND] = Last day of the current month

[MONTHSTART(2)] = First day of the month 2 months from now

[MONTHEND(-3)] = Last day of the month 3 months ago

[YEARSTART(num)]

[YEAREND(num)]

[YEARSTART(date|num)]

[YEAREND(date|num)]

Returns first or last date of the year. If num is empty then it uses today's date to find first or last of this year.

num - number of years from now. Use negative numbers to compute back years

date - If date value is supplied then the computation stars with that date instead of the current date.

[YEARSTART] = First day of the current year

[YEAREND] = Last day of the current year

[YEARSTART(2)] = First day of the year 2 years from now

[YEAREND(-3)] = Last day of the year 3 years ago

[DATETIME]

[DATETIMEUTC]

Current Date and Time in server's zone

Current Date and Time in UTC

7/16/2015 10:48

[DATETIMEFULL]

[DATETIMEFULLUTC]

Current Date and Time including seconds in server's zone

Current Date and Time including seconds in UTC

7/16/2015 10:48:34

[TIME]

[TIMEUTC]

Current Time in server's zone

Current Time in UTC

10:48 AM

[TIMEFULL]

[TIMEUTCFULL]

Current Time including in server's zone

Current Time including in UTC

10:48:34 AM

[DATE()]

[DATE(#|Period)]

[DATE(Date|#|Period)]

[DATE(Date|#|Period|Format)]

[DATE(Date|#|Period|Format|NextDayOfWeek)]

Computes a date that is # periods from the date supplied.

Date - The date to start computation. If this is not supplied then it defaults to today.

# - Number of periods from now to compute to. Default is 0.

Period - The period type to compute with. Default is D - days.

Possible date period values:

Y = Years as compared by year number

M = Months

D and d = Days

B = Business Days (M-F)

H and h = Hours

m = Minutes

S and s = Seconds

Format - Format of the returned date. See [FORMAT()] variable for more information.

[DATE()] - Current date

[DATE(4)] - Current date + 4 days

[DATE(4|B)] - Current date + 4 business days

[DATE(1/1/2024|4|B)] - 1/1/2024 + 4 business days

[DATEONLY(#|d)

Date # days from now. Returns DATE only.

7/16/2016

[DATE([EXDATE]||||MON)]

Next Monday after [EXDATE]

7/18/2016 12:34 PM

[DATE(DATE|Y|M|D|H|m|s)]

[DATEONLY(DATE|Y|M|D|H|m|s)]

[DATETIME(DATE|Y|M|D|H|m|s)]

[DATEADD(DATE|Y|M|D|H|m|s)]

Add to given date the number of Years, Months, Days, Hours minutes and Seconds provided.

You may omit any trailing zero values but must include any leading zeros

[DATE(1/1/2018|1)] = 1/1/2019 12:34 PM

[DATEONLY(1/1/2018|0|3)] = 4/1/2018

[DATETIME(1/1/2018|0|0|4)] = 1/5/2018 12:34 PM

[DATEADD(1/1/2018|0|0|4)] = 1/5/2018 12:34 PM

[TIMEADD()]

Works like DATEADD above. Kept for backward compatibility

[DAY] / [DOM]

[DAYUTC] / [DOMUTC]

New date can be sent in

Current day of month in server's zone

Current day of month in UTC

[DAY] = 12

[DAY(1/3/2016)] = 3

[DOW]

[DOWUTC]

New date can be sent in

Day of week in server's zone

Day of week in UTC

Return values are 0 - 6

0 = Sunday

1 = Monday

2 = Tuesday

3 = Wednesday

4 = Thursday

5 = Friday

6 = Saturday

[DOW] = 3

[DOW()] = 3

[DOW(1/3/2016)] = 0

[DOY]

[DOYUTC]

New date can be sent in

Day of year in server's zone

Day of year in UTC

[DOY] = 73

[DOY(1/3/2016)] = 3

[HOUR]

[HOURUTC]

Hour of time in 24-hour format in server's zone

Hour of time in 24-hour format in UTC

[HOUR] = 17 (for 5:23:15 PM)

[HOUR(1/1/2001 11:23:15 PM)] = 23

[MINUTE]

[MINUTEUTC]

Minute of time in server's zone

Minute of time in UTC

* There is no difference between the results of these two commands since UTC only differs in hours to the server time.

[MINUTE] = 13 (for 5:13:15 PM)

[MINUTE(1/1/2001 11:13:15 PM)] = 13

[SECOND]

[SECONDUTC]

Second of time in server's zone

Second of time in UTC

* There is no difference between the results of these two commands since UTC only differs in hours to the server time.

[SECOND] = 15 (for 5:13:15 PM)

[SECOND(1/1/2001 11:13:15 PM)] = 15

[ISDATE(Val|YesVal|NoVal)]

Checks to see if the invoice VAL is a date. If it is a date then the YesVal value is returned. If not then the NoVal is returned.

[ISDATE(1/1/2018|YEP|NOPE)] = YEP

[ISDATE(1/1/2018|1/1/2018|)] = 1/1/2018

[ISDATE(BadDate|YEP|NOPE)] = NOPE

This takes from 3 to 7 parameters

Possible date period values:

Y = Years as compared by year number

[DIFF(1/1/2009|12/31/2009|Y)] = 0

[DIFF(12/31/2009|1/1/2010|Y)] = 1

[DIFF(6/1/2009|5/30/2010|Y)] = 1

[DIFF(6/1/2009|6/1/2010|Y)] = 1

y = Years as compared by # of days

[DIFF(1/1/2009|12/31/2009|y)] = 0

[DIFF(12/31/2009|1/1/2010|y)] = 0

[DIFF(6/1/2009|5/30/2010|y)] = 0

[DIFF(6/1/2009|6/1/2010|y)] = 1

M = Months

D and d = Days

B = Business Days

H and h = Hours

m = Minutes

S and s = Seconds

StartHour - The first hour of the day that DIFF should include in 24-hour format. Leave blank to ignore.

EndHour - The last hour of the day that DIFF should include in 24-hour format. Leave blank to ignore. This is inclusive so any time that has this as its last hour is included. For instance, if the EndHour was 8 then a time of 8:20 is included because it is in the 8 AM hour.

StartDOW - The first day of the week that DIFF should include (1 to 7 where 1=Sunday, 7=Saturday). Leave blank to ignore.

EndDOW - The last day of the week that DIFF should include (0 to 6 where 0=Sunday, 6=Saturday). Leave blank to ignore.

* To make sure SECONDS works properly, you need to make sure the value you send in has seconds in it. Best to use [DATETIMEFULL] or its UTC counterpart to get the current date/time with seconds.

[DIFF(1/1/2014|1/4/2017|Y)] = 3 (3 years different)

[DIFF(1/1/2017 3:00 AM|1/4/2017 7:00 AM|H|6|16)] = 34

[FORMAT(date|pattern)]

Formats value based on its formatting rules according to .NET. Works on numeric and date formats.

Date Formats:

- Standard

- Custom

Numeric:

[FORMAT(12.3|c)] = $12.30

Date:

[FORMAT(1/31/2015|yy/dd)] = 15/01

[CLEANDATE(val|ret)]

Inspects date and if it is not a date the variable attempts to auto-correct known patterns. If the incoming value is a date then the return is a formatted date - mm/dd/yyyy.

Currently corrects the following patterns:

30-Jul-2020
JUL30,2020
July30,2020
30JUL20
9/30/20,
upon receipt (if 'receipt' found then uses current date)

val = value that should be a date.

ret = If it cannot auto-correct then this value is returned.

[CLEANDATE(1/1/2001)] = 1/1/2001

[CLEANDATE(Jan1,2001)] = 1/1/2001

[CLEANDATE(1JAN2001)] = 1/1/2001

[CLEANDATE(Upon Receipt)] = Current Date

[EPOCH()]

Returns the number of seconds elapsed since 1/1/1970. 1/1/1970 is the considered the beginning of time in UNIX and, as such, this is used in certain security applications. It is used mainly for interactions with external UNIX-style systems.

[EPOCH()] = 1696953933

[DATEFROMTERMS(Terms|StartDate)]

Returns a date that is computed from a terms value. This works with standard Terms values such as NET 30, 2/10 NET 30, COD, In Advance, Immediate, End of Month, 10th Of The Month, etc.

Terms = The terms value to be evaluated.

StartDate = Date to use for the start of the process such as the Invoice Date. Some calculations need to know that date to compute the Due Date such End of Month. Default is today - i.e. [DATE].

Here is a simple blog on standard invoice terms:

https://wise.com/us/blog/invoice-payment-terms

[DATEFROMTERMS(Net 30|11/21/2023)] = 12/21/2023

[DATEFROMTERMS(Immediate|11/21/2023)] = 11/21/2023

[DATEFROMTERMS(End of Month|11/21/2023)] = 11/30/2023

[DATEFROMTERMS(15th of Month|11/21/2023)] = 12/15/2023

[DATACHANGED(RecordID|Name)]

[DATACHANGEDUTC(RecordID|Name)]

Return last date/time a data item was changed

RecordID - ID of Record for Data element

Name - Name of Data Element

[DATACHANGED(123|LastName)] - 3/1/2024 11:00:00 AM