Date and time functions

use date and time functions to convert and transform date and time data for example, you can change the date format, convert time based on timezones, convert text to date or time data, and more below is a list of supported date and time functions with descriptions and details for each formatdate (date; format; \[timezone]) when to use it you have a item data types docid\ hdc1mr5jwoaqeiis266kb value that you wish to convert (format) to a item data types docid\ hdc1mr5jwoaqeiis266kb value (textual human readable representation) like 12 10 2019 20 30 or aug 18, 2019 10 00 am parameters the second column indicates the expected type if different type is provided, type coercion docid\ ihfbo as3zrc346cwrmmo is applied parameter expected type description date date date value to be converted to a text value format text format specified using tokens for date/time formatting docid\ kumukvbfixzztkuzye7xz example dd mm yyyy hh\ mm timezone text optional the timezone used for the conversion see list of tz database time zones , column "tz database name" for the list of recognized timezones if omitted, {{product name}} uses the organization's timezone you can manage time zones docid\ bn3s0 8azjdynxbnznp a return value and type text representation of the given date value according to the specified format and timezone type is text example the organization's and web's timezone were both set to europe/prague in the following examples function result formatdate(1 date created; mm/dd/yyyy ) 10/01/2018 formatdate(1 date created; yyyy mm dd hh\ mm a ) 2018 10 01 09 32 am formatdate(1 date created; dd mm yyyy hh\ mm ; utc ) 01 10 2018 07 32 formatdate(now; mm/dd/yyyy hh\ mm ) 19/03/2019 15 30 parsedate (text; format; \[timezone]) when to use it you have a item data types docid\ hdc1mr5jwoaqeiis266kb value representing a date (e g 12 10 2019 20 30 or aug 18, 2019 10 00 am ) and you wish to convert (parse) it to a item data types docid\ hdc1mr5jwoaqeiis266kb value (binary machine readable representation) parameters the second column indicates the expected type if different type is provided, type coercion docid\ ihfbo as3zrc346cwrmmo is applied parameter expected type description text text text value to be converted to a date value format text format specified using tokens for date/time formatting docid\ kumukvbfixzztkuzye7xz example dd mm yyyy hh\ mm timezone text optional the timezone used for the conversion see list of tz database time zones , column "tz database name" for the list of recognized timezones if omitted, {{product name}} uses the organization's timezone you can manage time zones docid\ bn3s0 8azjdynxbnznp a example europe/prague , utc return value and type date representation of the given text value according to the specified format and timezone type is date examples please note that in the following examples the returned date value is expressed according to iso 8601, but the actual resulting value is of type date parsedate( 2016 12 28 ; yyyy mm dd ) \= 2016 12 28t00 00 00 000z parsedate( 2016 12 28 16 03 ; yyyy mm dd hh\ mm ) \= 2016 12 28t16 03 00 000z parsedate( 2016 12 28 04 03 pm ; yyyy mm dd hh\ mm a ) \= 2016 12 28t16 03 06 000z parsedate( 1482940986 ; x ) \= 2016 12 28t16 03 06 000z adddays (date; number) returns a new date as a result of adding a given number of days to a date to subtract days, enter a negative number adddays( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 10t15 55 57 536z adddays( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 6t15 55 57 536z addhours (date; number) returns a new date as a result of adding a given number of hours to a date to subtract hours, enter a negative number addhours( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 08t17 55 57 536z addhours( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 08t13 55 57 536z addminutes (date; number) returns a new date as a result of adding a given number of minutes to a date to subtract minutes, enter a negative number addminutes( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 08t15 57 57 536z addminutes( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 08t15 53 57 536z addmonths (date; number) returns a new date as a result of adding a given number of months to a date to subtract months, enter a negative number addmonths( 2016 10 08t15 55 57 536z ; 2 ) \= 2016 12 08t15 57 57 536z addmonths( 2016 10 08t15 55 57 536z ; 2 ) \= 2016 08 08t15 57 57 536z addseconds (date; number) returns a new date as a result of adding a given number of seconds to a date to subtract seconds, enter a negative number addseconds( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 08t15 57 57 536z addseconds( 2016 12 08t15 55 57 536z ; 2 ) \= 2016 12 08t15 53 57 536z addyears (date; years) returns a new date as a result of adding a given number of years to a date to subtract years, enter a negative number addyears( 2016 12 08t15 55 57 536z ; 2 ) 2018 08 08t15 55 57 536z addyears( 2016 12 08t15 55 57 536z ; 2 ) 2014 08 08t15 55 57 536z setsecond (date; number) returns a new date with the seconds specified in parameters accepts numbers from 0 to 59 if a number is given outside of this range, it will return the date with the seconds from the previous or subsequent minute(s), accordingly setsecond( 2015 10 07t11 36 39 138z ; 10 ) \= 2015 10 07t11 36 10 138z setsecond( 2015 10 07t11 36 39 138z ; 61 ) \= 2015 10 07t11 37 01 138z setminute (date; number) returns a new date with the minutes specified in parameters accepts numbers from 0 to 59 if a number is given outside of this range, it will return the date with the minutes from the previous or subsequent hour(s), accordingly setminute( 2015 10 07t11 36 39 138z ; 10 ) \= 2015 10 07t11 10 39 138z setminute( 2015 10 07t11 36 39 138z ; 61 ) \= 2015 10 07t12 01 39 138z sethour (date; number) returns a new date with the hour specified in parameters accepts numbers from 0 to 59 if a number is given outside of this range, it will return the date with the hour from the previous or subsequent day(s), accordingly sethour( 2015 10 07t11 36 39 138z ; 10 ) \= 2015 08 07t06 36 39 138z sethour( 2015 10 07t11 36 39 138z ; 61 ) \= 2015 08 06t18 36 39 138z setday (date; number/name of the day in english) returns a new date with the day specified in parameters it can be used to set the day of the week, with sunday as 1 and saturday as 7 if the given value is from 1 to 7, the resulting date will be within the current (sunday to saturday) week if a number is given outside of the range, it will return the day from the previous or subsequent week(s), accordingly setday( 2018 06 27t11 36 39 138z ; monday ) \= 2018 06 25t11 36 39 138z setday( 2018 06 27t11 36 39 138z ; 1 ) \= 2018 06 24t11 36 39 138z setday( 2018 06 27t11 36 39 138z ; 7 ) \= 2018 06 30t11 36 39 138z setdate (date; number) returns a new date with the day of the month specified in parameters accepts numbers from 1 to 31 if a number is given outside of the range, it will return the day from the previous or subsequent month(s), accordingly setdate( 2015 08 07t11 36 39 138z ; 5 ) \= 2015 08 05t11 36 39 138z setdate( 2015 08 07t11 36 39 138z ; 32 ) \= 2015 09 01t11 36 39 138z setmonth (date; number/name of the month in english) returns a new date with the month specified in parameters accepts numbers from 1 to 12 if a number is given outside of this range, it will return the month in the previous or subsequent year(s), accordingly setmonth( 2015 08 07t11 36 39 138z ; 5 ) \= 2015 05 07t11 36 39 138z setmonth( 2015 08 07t11 36 39 138z ; 17 ) \= 2016 05 07t11 36 39 138z setmonth( 2015 08 07t11 36 39 138z ; january ) \= 2015 01 07t12 36 39 138z setyear (date; number) returns a new date with the year specified in parameters setyear( 2015 08 07t11 36 39 138zv ; 2017 ) \= 2017 08 07t11 36 39 138z calculate n th day of the week in a month if you need to calculate a date corresponding to the n th day of week in a month (e g 1st tuesday, 3rd friday, etc ), you can use the following formula {{adddays(setdate(1 date; 1); 1 n 7 formatdate(adddays(setdate(1 date; 1); " " + 1 dow); "e"))}} the formula contains the following items value description 1 n n th day 1 for 1 st tuesday 2 for 2 nd tuesday 3 for 3 rd tuesday etc 2 dow day of the week 1 for monday 2 for tuesday 3 for wednesday 4 for thursday 5 for friday 6 for saturday 7 for sunday 1 date the date determines the month to calculate n th day of week in current month use the now variable if you want to calculate only one specific case, e g 2nd wednesday, you may replace the items 1 n and 2 dow in the formula with the corresponding numbers for 2nd wednesday in the current month you would use the following values 1 n = 2 1 dow = 3 1 date = now setdate(now;1) returns first of current month formatdate( ;e) returns day of week (1, 2, 6) see the original source for the rest calculate days between dates {{round((2 value 1 value) / 1000 / 60 / 60 / 24)}} the values of d1 and d2 above have to be of type date if they are of type string (e g "20 10 2018"), use the parsedate() function to convert them to type date the round() function is used for cases when one of the dates falls within the daylight savings time period and the other does not in these cases, the difference in hours is by one hour less/more and dividing it by 24 gives a non integer results calculate the last day/millisecond of a month when specifying a date range (e g in a search module) spanning the whole previous month as a closed interval (the interval that includes both its limit points), it is necessary to calculate the last day of the month 2019 09 01 ≤ d ≤ 2019 09 30 {{adddays(setdate(now; 1); 1)}} in some cases, it is necessary to calculate not only the last day of month, but its last millisecond 2019 09 01t00 00 00 000z ≤ d ≤ 2019 09 30t23 59 59 999z {{parsedate(parsedate(formatdate(now; "yyyymm01"); "yyyymmdd"; "utc") 1; "x")}} if the result should respect your timezone settings, simply omit the utc argument {{parsedate(parsedate(formatdate(now; "yyyymm01"); "yyyymmdd") 1; "x")}} however, it is preferable to use a half open interval instead (the interval that excludes one of its limit points), specifying the first day of the following month instead and replacing the less or equal than operator with less than 2019 09 01 ≤ d < 2019 10 01 2019 09 01t00 00 00 000z ≤ d < 2019 10 01t00 00 00 000z transform seconds into hours, minutes and second {{floor(1 seconds / 3600)}} {{floor((1 seconds % 3600) / 60)}} {{((1 seconds % 3600) % 60)}} values of second should be number type this function is suited only if the second value is less than 86400 ( less than a day )