Articles in this section

Date & Time Functions

Patrik Šimek Updated April 18, 2019 08:04

formatDate (date; format; [timezone])

Parameters

The second column indicates the expected type. If different type is provided, Type Coercion is applied.

date	Date	Date value to be converted to a Text value.
format	Text	Format specified using Tokens for date/time formatting. 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, the Organization's timezone specified in your Profile settings will be applied (tab: Time zone options, section: Scenarios, row: the organization the scenario belongs to). Examples: `Europe/Prague`, `UTC`

Only the values listed in the "TZ database name" column are recognized by the function as a valid timezone. Any other value will be ignored and the Organization's timezone specified in your Profile will be used instead.

Return value and type

Text representation of the given Date value according to the specified format and timezone. Type is Text.

How Integromat displays Date values

Values of Date items in bundle inspector are formatted using the locale and Web's timezone specified in your Profile (tab: Time zone options, section: Web). It is possible to display a date item's value expressed according to ISO 8601 by hovering mouse cursor over the item.

If the ISO value does not show up, the item is not a Date but probably a Text.

Examples

The Organization's and Web's timezone were both set to Europe/Prague in the following examples.

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)
= 2018-10-01 07:32 AM

formatDate(now;DD.MM.YYYY HH:mm)
= 19.03.2019 15:30

parseDate (text; format; [timezone])

Parameters

The second column indicates the expected type. If different type is provided, Type Coercion is applied.

text	Text	Text value to be converted to a Date value.
format	Text	Format specified using Tokens for date/time parsing. 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, the Organization's timezone specified in your Profile settings will be applied (tab: Time zone options, section: Scenarios, row: the organization the scenario belongs to). Examples: `Europe/Prague`, `UTC`

Return value and type

Date representation of the given Text value according to the specified format and timezone. Type is Date.

How Integromat displays Date values

If the ISO value does not show up, the item is not a Date but probably a Text.

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-08-08T15:55:57.536Z;2)
= 2016-10-08T15:55:57.536Z

addMonths(2016-08-08T15:55:57.536Z;-2)
= 2016-06-08T15:55: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:55:59.536Z

addSeconds(2016-12-08T15:55:57.536Z;-2)
= 2016-12-08T15:55:55.536Z

addYears (datum;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-08-08T15:55:57.536Z;2)
= 2018-08-08T15:55:57.536Z

addYears(2016-08-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 the 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 23. If a number is given outside of the range, it will return the date with the hour from the previous or subsequent day(s), accordingly.

setHour(2015-08-07T11:36:39.138Z;6)
= 2015-08-07T04:36:39.138Z

setHour(2015-08-07T11:36:39.138Z;-6)
= 2015-08-06T16: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.138Z;2017)
= 2017-08-07T11:36:39.138Z

Examples

How to calculate days between dates

One possibility is to employ the following expression:

You can copy & paste the following code:

{{round((2.value - 1.value) / 1000 / 60 / 60 / 24)}}

NOTE: Values of D1 and D2 have to be of type Date. If they are of type String (e.g. "20.10.2018"), use parseDate() function to convert them to type Date.

INFO: The round() function is used for cases when one of the dates falls within the daylight savings time period and the other not. In these cases, the difference in hours is by one hour less/more and dividing it by 24 gives a non-integer results.

How to Calculate Date using Integromat Functions.

This can a little tricky, we’ve two Date/time Parsing function which you can use for this.

If the field type is Date then the formatDate function will work.

If the field type is text then you will need to use the parseDate function to convert that into a date.

Note: Make sure to check the field type before choosing the function for this calculation.

If you want to calculate days & hour it’s ideal to convert the Date into Milliseconds ( Timestamp) then perform the function. Token for converting it to Milliseconds is “X”

If the Date type is in Date format use this formula -

{{((formatDate(3.`Date 2`; "X") - formatDate(3.`Date 1`; "X")) / 60 / 60)}}

If the Date type is in Text format you will need first parse the date then do the calculation with the same formula.

{{(formatDate(parseDate(3.`Date 3`; "DD/MM/YYYY"); "X") - formatDate(parseDate(3.`Date 4`; "DD/MM/YYYY"); "X")) / 60 / 60 / 24 / 365}}

This calculation is for calculating the year, if you want in days remove 365. If you want in hours remove 24 & 365

Was this article helpful?

13 out of 21 found this helpful