Skip to main content

Date and Time functions

Function List:

Date(year, month, day)​

Returns a date composed of the specified year, month and day.

Date(2002, 3, 15)

DateAdd(dt, ts)​

Returns the date dt with the time span ts appended to it.

DateAdd(${StartDate}, ${Duration})

DateDiff(dt1, dt2)​

Returns the time span from dt2 to dt1 in milliseconds. The result is positive when dt1 is later than dt2, and negative when it is earlier. To get days, hours, or minutes, wrap the result in a time span function.

DateDiff(${FirstDate}, ${SecondDate})
InDays(DateDiff(${FirstDate}, ${SecondDate}))       // full days
TotalDays(DateDiff(Date(2026, 5, 19), Date(2026, 5, 1)))    // 18

DateFromUnixTimestamp(timestamp)​

Returns the date that is timestamp seconds after 1970-01-01 00:00:00 UTC.

DateFromUnixTimestamp(1700000000)    // 2023-11-14 22:13:20 UTC

DateNow()​

Returns the current date.

DateAdd(DateNow(), ${Duration})

DateParse(s)​

Constructs and returns a date based on string pattern s.

DateParse("20120227T132700")    // 2012-02-27 13:27:00.000

DateTime(year, month, day, hours, minutes, seconds, milliseconds)​

Returns a date composed of the specified parameters.

DateTime(2002, 3, 15, 23, 59, 45, 999)

DayOfMonth(dt)​

Returns the day of the month for the date dt.

DayOfMonth(Date(2021, 6, 14))    // 14

DayOfWeek(dt)​

Returns the day of the week for the date dt. The days of the week are numbered from 1 to 7.

DayOfWeek(Date(2020, 12, 31))    // 4

DayOfYear(dt)​

Returns the day of the year for the date dt.

DayOfYear(Date(2021, 2, 25))    // 56 (31 + 25)

Hour(dt)​

Returns the hour of the date dt.

Hour(DateTime(2002, 3, 15, 23, 59, 45, 999))    // 23

Millisecond(dt)​

Returns the millisecond of the date dt.

Millisecond(DateTime(2002, 3, 15, 23, 59, 45, 999))    // 999

Minute(dt)​

Returns the minute of the date dt.

Minute(DateTime(2002, 3, 15, 23, 59, 45, 999))    // 59

Month(dt)​

Returns the month of the date dt.

Month(DateTime(2002, 3, 15, 23, 59, 45, 999))    // 3

Quarter(dt)​

Returns the quarter of the date dt.

Quarter(Date(2002, 3, 15))    // 1

Second(dt)​

Returns the second of the date dt.

Second(DateTime(2002, 3, 15, 23, 59, 45, 999))    // 45

Time(hours, minutes, seconds, milliseconds)​

Returns a time composed of the specified parameters.

The time is actually a DateTime with a special insignificant date 0001-01-01.

Time(23, 59, 45, 999)    // 0001-01-01 23:59:45.999

TimeParse(s)​

Constructs and returns a time based on string pattern s.

The time is actually a DateTime with a special insignificant date 0001-01-01.

TimeParse("13:27")    // 0001-01-01 13:27:00.000

Today()​

Returns today's date.

Today()    // On January 1, 2021, would return the value 01-01-2021 00:00:00.000

UnixTimestamp(dt)​

Returns the number of microseconds from 1970-01-01 00:00:00 UTC to the date dt. This is the unit of a datetime axis, so use it to place a formula line at a date. It is not the inverse of DateFromUnixTimestamp, which takes seconds.

UnixTimestamp(Date(2011, 1, 1))    // 1293840000000000
${Started} = UnixTimestamp(Date(2011, 1, 1))    // a formula line at January 1, 2011

Weeknum(dt)​

Returns the week number of the date dt.

Weeknum(Date(2021, 2, 3))    // 5

Year(dt)​

Returns the year of the date dt.

Year(Date(2021, 2, 3))    // 2021