Date and time functions in Adobe Workfront Fusion
Access requirements
You must have the following access to use the functionality in this article:
To find out what plan, license type, or access you have, contact your Workfront administrator.
For information on Adobe Workfront Fusion licenses, see Adobe Workfront Fusion licenses.
formatDate (date; format; [timezone])
Use this function when you have a Date value, such as 12-10-2021 20:30
, that you want to format as a Text value, such as Dec 10, 2021 8:30 PM
.
This is useful, for example, when you need to change the date format of one app or webservice to that of a connected app or web service in the same scenario.
For more information, see Date and Text in the article Item data types in Adobe Workfront Fusion.
Parameters
Lets you specify a format using date/time formatting tokens. For more information, see Tokens for date and time formatting in Adobe Workfront Fusion.
Example: DD.MM.YYYY HH:mm
(Optional) Lets you specify the timezone used for the conversion.
For the list of recognized timezones, see the "TZ database name" column in the Wikipedia List of tz database time zones. Only the values listed in this column are recognized by the function as a valid timezone. Any other value is ignored and the Scenarios timezone specified in your Profile is used instead. For more information, see in the article Change profile settings in Adobe Workfront Fusion.
If you omit this parameter, the Scenarios timezone specified in your Profile settings is applied.
Example: Europe/Prague
, UTC
If different type is provided, type coercion is applied. For more information, see Type coercion in Adobe Workfront Fusion.
Return value and type
The formatDate
function returns a text representation of the given Date value according to the specified format and timezone. The data type is Text.
Europe/Prague
in these examples.-
formatDate(1. Date created;MM/DD/YYYY)
Returns 10/01/2018
-
formatDate(1. Date created; YYYY-MM-DD hh:mm A)
Returns 2018-10-01 09:32 AM
-
formatDate(1. Date created;DD.MM.YYYY HH:mm;UTC)
Returns 01.10.2018 07:32
-
formatDate(now;DD.MM.YYYY HH:mm)
Returns 19.03.2019 15:30
parseDate (text; format; [timezone])
Use this function when you have a Text value representing a date (such as 12-10-2019 20:30
or Aug 18, 2019 10:00 AM
) and you want to convert (parse) it to a Date value (a binary machine readable representation). For more information, see Date and Text in the article Item data types in Adobe Workfront Fusion.
Parameters
The second column indicates the expected type. If different type is provided, type coercion is applied. For more information, see Type coercion in Adobe Workfront Fusion.
Lets you specify a format using date/time formatting tokens. For more information, see Tokens for date and time formatting in Adobe Workfront Fusion.
Example: DD.MM.YYYY HH:mm
(Optional) Lets you specify the timezone used for the conversion.
For the list of recognized timezones, see the "TZ database name" column in the Wikipedia List of tz database time zones. Only the values listed in this column are recognized by the function as a valid timezone. Any other value is ignored and the Scenarios timezone specified in your Profile is used instead. For more information, see in the article Change profile settings in Adobe Workfront Fusion.
If you omit this parameter, the Scenarios timezone specified in your Profile settings is applied.
Example: Europe/Prague
, UTC
If different type is provided, type coercion is applied. For more information, see Type coercion in Adobe Workfront Fusion.
Return value and type
This function converts a text string to a date, according to the format and timezone that you specify. The data type of the value is Date.
-
parseDate(2016-12-28;YYYY-MM-DD)
Returns 2016-12-28T00:00:00.000Z
-
parseDate(2016-12-28 16:03;YYYY-MM-DD HH:mm)
Returns 2016-12-28T16:03:00.000Z
-
parseDate(2016-12-28 04:03 pm; YYYY-MM-DD hh:mm a)
Returns 2016-12-28T16:03:06.000Z
-
parseDate(1482940986;X)
addDays (date; number) 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)
Returns 2016-12-10T15:55:57.536Z
-
addDays(2016-12-08T15:55:57.536Z;-2)
Returns 2016-12-6T15:55:57.536Z
addHours (date; number) 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)
Returns 2016-12-08T17:55:57.536Z
-
addHours(2016-12-08T15:55:57.536Z;-2)
Returns 2016-12-08T13:55:57.536Z
addMinutes (date; number) 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)
Returns 2016-12-08T15:57:57.536Z
-
addMinutes(2016-12-08T15:55:57.536Z;-2)
Returns 2016-12-08T15:53:57.536Z
addMonths (date; number) addseconds-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)
Returns 2016-10-08T15:55:57.536Z
-
addMonths(2016-08-08T15:55:57.536Z;-2)
Returns 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)
Returns 2016-12-08T15:55:59.536Z
-
addSeconds(2016-12-08T15:55:57.536Z;-2)
Returns 2016-12-08T15:55:55.536Z
addYears (date; number)
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)
Returns 2018-08-08T15:55:57.536Z
-
addYears(2016-12-08T15:55:57.536Z; -2)
Returns 2014-08-08T15:55:57.536Z
setSecond (date; number)
This function returns a new date with the seconds specified in parameters.
Specify a number from 0 to 59. If the number is outside of that range, the function returns a second from the previous minute (for a negative number) or subsequent minute (for a positive number).
If you need to specify a number outside the range, we recommend that you use addSeconds, as described above in the section addSeconds (date; number).
-
setSecond(2015-10-07T11:36:39.138Z;10)
Returns 2015-10-07T11:36:10.138Z
-
setSecond(2015-10-07T11:36:39.138Z; 6)
Returns 2015-10-07T11:37:01.138Z
setMinute (date; number)
This function returns a new date with the minutes specified in parameters.
Specify a number from 0 to 59. If the number is outside of that range, the function returns a minute from the previous hour (for a negative number) or subsequent hour (for a positive number).
If you need to specify a number outside the range, we recommend that you use addMinutes, as described above in addMinutes (date; number).
-
setMinute(2015-10-07T11:36:39.138Z;10)
Returns 2015-10-07T11:10:39.138Z
-
setMinute(2015-10-07T11:36:39.138Z;61)
Returns 2015-10-07T12:01:39.138Z
setHour (date; number)
This function returns a new date with the hour specified in parameters.
Specify a number from 0 to 23. If the number is outside this range, the function returns an hour from the previous day (for a negative number) or subsequent day (for a positive number).
If you need to specify a number outside the range, we recommend that you use addHours, as described above in addHours (date; number).
-
setHour(2015-08-07T11:36:39.138Z;6)
Returns 2015-08-07T06:36:39.138Z
-
setHour(2015-08-07T11:36:39.138;-6)
Returns 2015-08-06T18:36:39.138Z
setDay (date; number/name of the day in English)
This function returns a new date with the day specified in parameters.
You can use this function to set the day of the week, with Sunday as 1 and Saturday as 7. If the you specify a number from 1 to 7, the resulting date is within the current (Sunday-to-Saturday) week. If the number is outside of that range, the function returns a day from the previous week (for a negative number) or subsequent week (for a positive number).
If you need to specify a number outside the range, we recommend that you use addDays, as described above in addDays (date; number).
-
setDay(2018-06-27T11:36:39.138Z;Monday)
Returns 2018-06-25T11:36:39.138Z
-
setDay(2018-06-27T11:36:39.138Z;1)
Returns 2018-06-24T11:36:39.138Z
-
setDay(2018-06-27T11:36:39.138Z;7)
Returns 2018-06-30T11:36:39.138Z
setDate (date; number)
This function returns a new date with the day of the month specified in parameters.
Specify a number from 1 to 31. If the number is outside of this range, the function returns a day from the previous month (for a negative number) or subsequent month (for a positive number).
-
setDate(2015-08-07T11:36:39.138Z;5)
Returns 2015-08-05T11:36:39.138Z
-
setDate(2015-08-07T11:36:39.138Z;32)
Returns 2015-09-01T11:36:39.138Z
setMonth (date; number/name of the month in English)
This function returns a new date with the month specified in parameters.
Specify a number from 1 to 12. If the number is outside of this range, the function returns the month in the previous year (for a negative number) or subsequent year (for a positive number).
-
setMonth(2015-08-07T11:36:39.138Z;5)
Returns 2015-05-07T11:36:39.138Z
-
setMonth(2015-08-07T11:36:39.138Z;17)
Returns 2016-05-07T11:36:39.138Z
-
setMonth(2015-08-07T11:36:39.138Z;january)
Returns 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)
Returns 2017-08-07T11:36:39.138Z
dateDifference (Date1; Date2; Unit)
Returns a number representing the difference in the two dates, expressed in the specified unit.
Date2 is subtracted from Date1.
Use one of the following time values for the unit
parameter:
- milliseconds
- seconds
- minutes
- hours
- days
- weeks
- months
If no unit is specified, the function returns the difference in milliseconds.
-
dateDifference(2021-05-11T18:10:00.000Z;2021-05-11T18:00:00.000Z)
Returns
600,000
-
dateDifference(2021-05-11T18:10:00.000Z;2021-05-11T18:00:00.000Z;hours)
Returns
4
-
dateDifference2021-06-11T18:10:00.000Z;2021-05-11T18:00:00.000Z;months)
Returns
1
Additional examples
How to calculate n-th day of week in month
This section is adapted for Workfront Fusion from the Exceljet web page that explains how to get the nth day of week in a month.
If you need to calculate a date corresponding to n-th day of week in month (for example, first Tuesday, third Friday, and so on), 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:
In case you want to calculate only one specific case, for example, every second Wednesday, you can replace the items 1.n
and 2.dow
in the formula with corresponding numbers. For the second Wednesday in the current month, you would use the following values:
1.n
=2
1.dow
=3
1.date
=now
Explanation:
setDate(now;1)
returns first of current monthformatDate(....;E)
returns day of week (1, 2, … 6)
How to calculate days between dates
One possibility is to employ the following expression:
{{round((2.value - 1.value) / 1000 / 60 / 60 / 24)}}
-
Values of
D1
andD2
have be Date type values. If they are String type values (for example, 20.10.2018), use theparseDate()
function to convert them to Date type values. -
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 one hour less or more. You can divide it by 24 for a non-integer result. You lose an hour-Daylight Savings. Round flattens it so you don’t have a percentage
How to calculate last day/millisecond of month
When you specify a date range, for example in a search module, if the range spans the whole previous month as a closed interval (the interval that includes both its limit points), you need to calculate the last day of the month.
2019-09-01 ≤ D ≤ 2019-09-30
The formula below shows one way how to calculate last day of the previous month:
{{addDays(setDate(now; 1); -1)}}
In some cases, you need to calculate not only the last day of month, but literally its last millisecond:
2019-09-01T00:00:00.000Z ≤ D ≤ 2019-09-30T23:59:59.999Z
This formula shows one way how to calculate last millisecond of the previous month:
{{parseDate(parseDate(formatDate(now; "YYYYMM01"); "YYYYMMDD"; "UTC") - 1; "x")}}
If you need the result to use your timezone setting, omit the UTC argument:
{{parseDate(parseDate(formatDate(now; "YYYYMM01"); "YYYYMMDD") - 1; "x")}}
However, it is preferable to use 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” as follows:
2019-09-01 ≤ D < 2019-10-01
2019-09-01T00:00:00.000Z ≤ D < 2019-10-01T00:00:00.000Z