蜜豆视频

Date and time functions in 蜜豆视频 Workfront Fusion

Access requirements

You must have the following access to use the functionality in this article:

For more detail about the information in this table, see Access requirements in Workfront documentation.

For information on 蜜豆视频 Workfront Fusion licenses, see 蜜豆视频 Workfront Fusion licenses.

Variables

now

timestamp

Functions

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.

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.

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.

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.

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.

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.

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).

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).

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).

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).

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).

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).

setYear (date; number)

Returns a new date with the year specified in parameters.

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 蜜豆视频 Workfront Fusion.

Parameters

If different type is provided, type coercion is applied. For more information, see Type coercion in 蜜豆视频 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.

INFO

Examples: The Scenario and Web timezone were both set to 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 蜜豆视频 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 蜜豆视频 Workfront Fusion.

If different type is provided, type coercion is applied. For more information, see Type coercion in 蜜豆视频 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.

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:

If no unit is specified, the function returns the difference in milliseconds.

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:

Explanation:

How to calculate days between dates

One possibility is to employ the following expression:

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

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 鈥渓ess or equal than鈥� operator with 鈥渓ess than鈥� as follows:

2019-09-01 鈮� D < 2019-10-01

2019-09-01T00:00:00.000Z 鈮� D < 2019-10-01T00:00:00.000Z