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; Locale; TimeZone)

Converts a text representation of a date to a number.

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

• Standard formats for the specified locale.

• Jira formats, as specified in the Jira's system settings.

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. 

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

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. 

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.


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.

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; Locale; 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.

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

• Standard formats for the specified locale.

• Jira formats, as specified in the Jira's system settings.

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.

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.

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

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.

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.

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.

Example:

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

MINUTE

MINUTE(DateTime)

Returns the minutes in the specified date/time value.

Example:

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

MONTH

MONTH(DateTime)

Returns the month in the specified date/time value.

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.

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.

Example:

• NOW()

PARSE_DATETIME

PARSE_DATETIME(Text, Format, Locale

, TimeZone)

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

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.

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. 

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.

Example:

• TODAY()

TRUNCATE_TIME

TRUNCATE_TIME(DateTime)

Removes the time value from the date/time.

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.

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.

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.

Example:

• TRUNCATE_TO_SECONDS(NOW())

WEEKDAY

WEEKDAY(DateTime)

Returns the number of the day of the week.

Example:

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

WEEKNUM

WEEKNUM(DateTime)

Returns the number of the week of the year.

Example:

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

YEAR

YEAR(DateTime)

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

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.

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