Date and Time Functions

Date/time functions operate with a numeric representation of time. A moment in time is represented as a number of milliseconds since midnight, January 1st 1970, GMT. Negative values are allowed, representing times prior to January 1st 1970.

To display a result of a date/time calculation in a readable way, you need to either configure the Formula to use a date/time format, or use one of the conversion functions to turn the value into a human-readable text.

Many of the date / time functions depend on the current user's time zone.

DATE

DATE(Text; TimeZone)

Converts a text representation of a date to a number.

Parameter	Type	Description
`Text`	`Text/Each`	The text value to convert.
`TimeZone` (Optional)	`Text`	Optional time zone identifier, such as "America/New_York".
→ Result	Number	Timestamp, corresponding to midnight of the specified date at the specified time zone. If conversion is unsuccessful, returns an error.

The conversion uses the standard formats for representing dates:

Format "yyyy-MM-dd", like "2017-04-15".

Examples:

DATE("2016-01-01")
DATE("31/Dec/16")
DATE("12/31/2016", "en_US", "America/New_York")

DATE_ADD

DATE_ADD(DateTime, Number, Unit)

Adds the specified integer number of seconds, minutes, hours, days, months or years to the date or date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
`Number`	`Integer`	The integer number of units of time to add.
`Unit`	`Text`	A text value specifying the unit of time: `"seconds"`, `"minutes"`, `"hours"`, `"days"`, `"months"`, `"years"`
→ Result	`Date`	Adjusted `DATETIME`.

Examples:

DATE_ADD(DATE("2016-01-31"), 1, "day") → DATE("2016-02-01")
DATE_ADD(DATE("2016-01-31"), 1, "month") → DATE("2016-02-29")
DATE_ADD(DATE("2016-02-29"), 1, "year") → DATE("2017-02-28")
DATE_ADD(DATETIME("2016-01-31 10:30:00"), 3, "hours") → DATETIME("2016-01-31 13:30:00")
DATE_ADD(DATETIME("2016-01-31 23:59:59"), 2, "minutes") → DATETIME("2016-02-01 00:01:59")

The second parameter (Number) must be an integer.

DATE_SET

DATE_SET(DateTime, Number, Unit)

Sets the specified part of the date or date/time to the specific integer value. Note that unlike DATE_ADD and DATE_SUBTRACT, you can specify additional units like "day_of_week".

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
`Number`	`Integer`	The integer number to be set as the unit value in this date/time.
`Unit`	`Text`	A text value specifying the unit of time: `"second"`, `"minute"`, `"hour"`, `"day"`, `"month"`, `"year", "day_of_week".`
→ Result	`Date`	Adjusted `DATETIME`.

Examples:

DATE_SET(DATE("2016-01-31"), 2017, "year") → DATE("2017-01-31")
DATE_SET(DATE("2016-01-31"), 2, "month") → DATE("2016-02-29")
DATE_SET(DATETIME("2016-02-29 15:30"), 10, "day") → DATETIME("2016-02-10 15:30")
DATE_SET(DATE("2017-04-01"), 7, "day_of_week") → DATE("2017-04-02")
DATE_SET(DATETIME("2016-01-31 10:30:00"), 0, "hour") → DATETIME("2016-01-31 00:30:00")

DATE_SUBTRACT

DATE_SUBTRACT(DateTime, Number, Unit)

Subtracts the specified integer number of seconds, minutes, hours, days, months or years from the date or date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
`Number`	`Integer`	The integer number of units of time to subtract.
`Unit`	`Text`	A text value specifying the unit of time: `"seconds"`, `"minutes"`, `"hours"`, `"days"`, `"months"`, `"years"`
→ Result	`Date`	Adjusted `DATETIME`.

Examples:

DATE_SUBTRACT(DATE("2016-02-01"), 1, "day") → DATE("2016-01-31")
DATE_SUBTRACT(DATE("2016-02-29"), 1, "month") → DATE("2016-01-29")
DATE_SUBTRACT(DATE("2017-02-28"), 1, "year") → DATE("2016-02-28")
DATE_SUBTRACT(DATETIME("2016-01-31 10:30:00"), 3, "hours") → DATETIME("2016-01-31 07:30:00")
DATE_SUBTRACT(DATETIME("2016-02-01 00:01:59"), 2, "minutes") → DATETIME("2016-01-31 23:59:59")

DAY

DAY(DateTime)

Returns the day of the month for the given date or date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	Number	Numeric value for the day of the month. The result is calculated using the current user's time zone.

Example:

DAY(DATE("2017-04-15")) → 15

DAYS_BETWEEN

DAYS_BETWEEN(DateTime1, DateTime2)

Calculates the number of full days (24 hour periods) between two date or date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date or date/time value to compare.
`DateTime2`	`Date`	Second date or date/time value to compare.
→ Result	Number	The number of full days between the dates. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

DAYS_BETWEEN(DATE("2017-01-01"), DATE("2017-02-01")) → 31
DAYS_BETWEEN(DATE("2017-01-01"), DATE("2017-01-01")) → 0
DAYS_BETWEEN(DATE("2017-01-01"), DATE("2016-01-01")) → -366
DAYS_BETWEEN(DATETIME("2017-01-01 00:00"), DATETIME("2017-01-01 23:59")) → 0
DAYS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 23:58")) → 0
DAYS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 23:59")) → 1

DATETIME

DATETIME(Text; TimeZone)

Converts a text representation of a date and time to a numeric representation of that date and time. The resulting timestamp will correspond to the specified date and time at the specified time zone. If seconds are omitted, they will be set to zero.

Parameter	Type	Description
`Text`	`Text/Each`	The text value to convert.
`TimeZone` (Optional)	`Text`	Optional time zone identifier, such as "America/New_York".
→ Result	Number	Timestamp, corresponding to the specified date and time at the specified time zone. If seconds are omitted, they will be set to zero. If conversion is unsuccessful, returns an error.

The conversion uses the standard formats for representing dates:

Format "yyyy-MM-dd HH:mm:ss" and the same without seconds, like "2017-04-15 15:00" or "2017-12-31 23:59:59" (using 24-hour clock).

Examples:

DATETIME("2016-01-01 00:01")
DATETIME("31/Dec/16 3:15 pm")
DATETIME("12/31/2016, 3:15 PM", "en_US", "America/New_York")

END_OF_MONTH

END_OF_MONTH(DateTime)

Sets the day in the date/time value to the end of the month. Does not change the time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Date`	Date is set to end of the month. Time value is unchanged.

Example:

END_OF_MONTH(DATE("2017-04-15")) → DATE("2017-04-30")

FORMAT_DATETIME

FORMAT_DATETIME(DateTime, Format, Locale, TimeZone)

Advanced function to convert a date/time value into a text. Accepts an arbitrary format text and, optionally, locale and time zone settings. Does not depend on the current user's locale nor time zone.

Parameter	Type	Description
`DateTime`	`Date/Each`	The value to convert.
`Format`	`Text`	The format string. For all the options, please see Java documentation for SimpleDateFormat.
`Locale` (Optional)	`Text`	Optional locale identifier. If omitted or `undefined`, will use Jira's system locale. (Not the user's locale!)
`TimeZone` (Optional)	`Text`	Optional time zone identifier. If omitted or `undefined`, will use Jira's system time zone. (Not the user's time zone!)
→ Result	Text	DateTime converted to text.

Examples:

FORMAT_DATETIME(DATE("2017-04-15"), "EEE, MMM d, `yy", "fr_FR") → "sam., avr. 15, `17"
FORMAT_DATETIME(DATETIME("2016-12-31 23:59"), "yyyy-MM-dd'T'HH:mm:ss") → "2016-12-31T23:59:00"

HOUR

HOUR(DateTime)

Returns the hour in the specified date/time value (from 0 to 23).

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the hour (0 to 23).

Example:

HOUR(DATETIME("2017-01-01 20:15")) → 20

HOURS_BETWEEN

HOURS_BETWEEN(DateTime1, DateTime2)

Calculates the number of full hours between two date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date/time value to compare.
`DateTime2`	`Date`	Second date/time value to compare.
→ Result	`Number`	The number of full hours between the date/time values. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

HOURS_BETWEEN(DATE("2017-01-01"), DATE("2017-01-02")) → 24
HOURS_BETWEEN(DATETIME("2017-01-01 15:00"), DATETIME("2017-01-01 16:30")) → 1
HOURS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 00:58")) → 0
HOURS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 00:59")) → 1

MAKE_DATE

MAKE_DATE(Year, Month, Day)

Creates a date value based on the numbers defining year, month and day. The time is set to midnight in the user's time zone.

Parameter	Type	Description
`Year`	`Number`	Year.
`Month`	`Number`	Number corresponding to the month.
`Day`	`Number`	Number corresponding to the day of the month.
→ Result	`Date`	Date/time set to midnight in the current user's time zone.

Example:

MAKE_DATE(2017, 12, 31)

MAKE_DATETIME

MAKE_DATETIME(Year, Month, Day, Hour, Minute, Second)

Creates a date/time value based on the numbers defining year, month, day, hour, minute and second.

Parameter	Type	Description
`Year`	`Number`	Year.
`Month`	`Number`	Number corresponding to the month.
`Day`	`Number`	Number corresponding to the day of the month.
`Hour`	`Number`	Hour (0-23).
`Minute`	`Number`	Minutes.
`Second`	`Number`	Seconds.
→ Result	`Date`	Date/time based on the provided values. Set to the current user's time zone.

Example:

MAKE_DATETIME(2017, 12, 31, 23, 59, 59)

MINUTE

MINUTE(DateTime)

Returns the minutes in the specified date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Minutes (from 0 to 59)

Example:

MINUTE(DATETIME("2017-01-01 20:15")) → 15

MONTH

MONTH(DateTime)

Returns the month in the specified date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the month (1 to 12).

Example:

MONTH(DATE("2017-04-15")) → 4

MONTHS_BETWEEN

MONTHS_BETWEEN(DateTime1, DateTime2)

Calculates the number of months between two date or date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date or date/time value to compare.
`DateTime2`	`Date`	Second date or date/time value to compare.
→ Result	`Number`	The number of full months between the date/time values. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

MONTHS_BETWEEN(DATE("2017-01-01"), DATE("2018-01-01")) → 12
MONTHS_BETWEEN(DATE("2017-01-31"), DATE("2017-02-28")) → 0
MONTHS_BETWEEN(DATE("2017-02-28"), DATE("2017-04-28")) → 2
MONTHS_BETWEEN(DATE("2017-01-01"), DATE("2016-12-01")) → -1

NOW

NOW()

Returns the current date and time.

Parameter	Type	Description
→ Result	`Date`	Current date and time.

Example:

NOW()

PARSE_DATETIME

PARSE_DATETIME(Text, Format

, TimeZone)

Advanced function to convert a text into a date or date/time value. Accepts an arbitrary format string and, optionally, time zone settings. Does not depend on the current user's time zone.

Parameter	Type	Description
`Text`	`Text/Each`	The value to convert.
`Format`	`Text`	The format string. For all the options, please see Java documentation for SimpleDateFormat.
`TimeZone` (Optional)	`Text`	Optional time zone identifier.
→ Result	`Date`	Date or date/time value for the original `Text`.

Examples:

PARSE_DATETIME("sam., avr. 15, `17", "EEE, MMM d, `yy", "fr_FR") → DATE("2017-04-15")
PARSE_DATETIME("2016-12-31T23:59:00", "yyyy-MM-dd'T'HH:mm:ss") → DATETIME("2016-12-31 23:59")

SECOND

SECOND(DateTime)

Returns the seconds in the specified date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Seconds (0 to 59).

Example:

SECOND(DATETIME("2017-04-15 15:30:59")) → 59

START_OF_MONTH

START_OF_MONTH(DateTime)

Sets the day in the date/time value to the first day of the month.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Date`	Date is set to first day of the month. Time value is unchanged.

Example:

START_OF_MONTH(DATE("2017-04-15")) → DATE("2017-04-01")

TODAY

TODAY()

Returns the current date with time set to midnight according to the current user's time zone.

Parameter	Type	Description
→ Result	`Date`	Current date

Example:

TODAY()

TRUNCATE_TIME

TRUNCATE_TIME(DateTime)

Removes the time value from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date, set to midnight in the current user's time zone.

Example:

TRUNCATE_TIME(DATETIME("2017-01-01 15:15")) → DATE("2017-01-01")

TRUNCATE_TO_HOURS

TRUNCATE_TO_HOURS(DateTime)

Removes the minutes, seconds and milliseconds from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date/time, set to the last even hour in the current user's time zone.

Example:

TRUNCATE_TO_HOURS(DATETIME("2017-01-01 15:15")) → DATE("2017-01-01 15:00")

TRUNCATE_TO_MINUTES

TRUNCATE_TO_MINUTES(DateTime)

Removes the seconds and milliseconds from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date/time, set to the last even minute.

Example:

TRUNCATE_TO_MINUTES(DATETIME("2017-01-01 15:15:15")) → DATE("2017-01-01 15:15:00")

TRUNCATE_TO_SECONDS

TRUNCATE_TO_SECONDS(DateTime)

Removes the milliseconds from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date/time, with milliseconds removed.

Example:

TRUNCATE_TO_SECONDS(NOW())

WEEKDAY

WEEKDAY(DateTime)

Returns the number of the day of the week.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the day of the week. Follows ISO-8601 standard (1 – Monday, 7 – Sunday).

Example:

WEEKDAY(DATE("2017-04-23")) → 7

WEEKNUM

WEEKNUM(DateTime)

Returns the number of the week of the year.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the week of the year. Follows ISO-8601 standard.

Example:

WEEKNUM(DATE("2017-01-02")) → 2

YEAR

YEAR(DateTime)

Returns the year in a date or date/time value as a number.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Integer`	Year.

Example:

YEAR(DATE("2017-04-23")) → 2017

YEARS_BETWEEN

YEARS_BETWEEN(DateTime1, DateTime2)

Calculates the number of years between two date or date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date or date/time value to compare.
`DateTime2`	`Date`	Second date or date/time value to compare.
→ Result	`Number`	The number of full years between the date/time values. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

YEARS_BETWEEN(DATE("2017-01-01"), DATE("2018-01-01")) → 1
YEARS_BETWEEN(DATE("1703-05-27"), DATE("2017-04-23")) → 313
YEARS_BETWEEN(DATE("2017-06-01"), DATE("2018-05-31")) → 0