Skip to main content
Skip table of contents

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.

ParameterTypeDescription
TextText/EachThe text value to convert.
TimeZone (Optional)TextOptional time zone identifier, such as "America/New_York". 
ResultNumberTimestamp, 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. 

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
NumberIntegerThe integer number of units of time to add.
UnitTextA text value specifying the unit of time: "seconds""minutes""hours""days""months""years"
ResultDateAdjusted 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".

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
NumberIntegerThe integer number to be set as the unit value in this date/time.
UnitText A text value specifying the unit of time: "second""minute""hour""day""month""year", "day_of_week".
ResultDateAdjusted 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. 

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
NumberIntegerThe integer number of units of time to subtract.
UnitTextA text value specifying the unit of time: "seconds""minutes""hours""days""months""years"
ResultDateAdjusted 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.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultNumberNumeric 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.

ParameterTypeDescription
DateTime1DateFirst date or date/time value to compare.
DateTime2DateSecond date or date/time value to compare.
ResultNumberThe 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.


ParameterTypeDescription
TextText/EachThe text value to convert.
TimeZone (Optional)TextOptional time zone identifier, such as "America/New_York". 
ResultNumberTimestamp, 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.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultDateDate 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

DateTimeDate/EachThe value to convert.
FormatTextThe format string. For all the options, please see Java documentation for SimpleDateFormat.
Locale (Optional)TextOptional locale identifier. If omitted or undefined, will use Jira's system locale. (Not the user's locale!)
TimeZone (Optional)TextOptional time zone identifier. If omitted or undefined, will use Jira's system time zone. (Not the user's time zone!)
→ ResultTextDateTime 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).

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric 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.

ParameterTypeDescription
DateTime1DateFirst date/time value to compare.
DateTime2DateSecond date/time value to compare.
ResultNumberThe 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.

ParameterTypeDescription
YearNumberYear.
MonthNumberNumber corresponding to the month.
DayNumberNumber corresponding to the day of the month.
ResultDateDate/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.

ParameterTypeDescription
YearNumberYear.
MonthNumberNumber corresponding to the month.
DayNumberNumber corresponding to the day of the month.
HourNumberHour (0-23).
MinuteNumberMinutes.
SecondNumberSeconds.
ResultDateDate/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.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberMinutes (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.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric 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.

ParameterTypeDescription
DateTime1DateFirst date or date/time value to compare.
DateTime2DateSecond date or date/time value to compare.
ResultNumberThe 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.

ParameterTypeDescription
ResultDateCurrent 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.

ParameterTypeDescription
TextText/EachThe value to convert.
FormatTextThe format string. For all the options, please see Java documentation for SimpleDateFormat.
TimeZone (Optional)TextOptional time zone identifier.
ResultDateDate 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.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberSeconds (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. 

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultDateDate 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.

ParameterTypeDescription
ResultDateCurrent date

Example:

  • TODAY()

TRUNCATE_TIME

TRUNCATE_TIME(DateTime)

Removes the time value from the date/time.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate, 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.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate/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.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate/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.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate/time, with milliseconds removed.

Example:

  • TRUNCATE_TO_SECONDS(NOW())

WEEKDAY

WEEKDAY(DateTime)

Returns the number of the day of the week.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric 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.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric 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.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultIntegerYear.

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.

ParameterTypeDescription
DateTime1DateFirst date or date/time value to compare.
DateTime2DateSecond date or date/time value to compare.
ResultNumberThe 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


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.