DATE_FORMAT Function: Displaying Date and Time Using Formulas

You can use the DATE_FORMAT function in a formula to change the date and time format and the time zone. The DATE_FORMAT function is also used when concatenating date and time with other strings.

DATE_FORMAT(Formula, "Date and time format", "Time zone")

Parameters of the DATE_FORMAT function is used in any of the following format:

Parameters in the Function Format

Item Description
Formula

For formula, the following fields can be used:

  • Date and time
    This field can be used only when the date and time is between 1970-1-1 AM 00:00 UTC (AM 9:00 JST) and 2050-1-1 AM 00:00 UTC (AM 9:00 JST).
  • Date
    This field can be used only when the date is between 1970-1-1 and 2286-11-20.
  • Time
  • Created datetime
  • Updated datetime
  • Number
  • Calculated

Specify a field with a field code. To add or subtract time to/from date and time, specify time in seconds in the formula. For example, specify "3600" or "60*60*1" for one hour. For one day, specify "24*60*60*1".
Example: Date + (60 * 60 * 1)

If the result of the formula is in number, the number is treated as UNIX time. UNIX time is the number of seconds elapsed since 1970-1-1 AM 00:00 UTC. It is also called UNIX time stamp. If the date and time is 2016-4-1 AM 00:00 (UTC), the UNIX time is "1459468800".
UNIX time Specify UNIX time that is less than 10,000,000,000.
Date and time format Specify the date and time format of the converted value.
For the format, see the following section:
Date and Time Format
Time Zone

Specify the changed time zone ID or "system". When "system" is specified, the standard time zone that is set by the administrator is used.
The time zone of the field for date and time is fixed at Coordinated Universal Time. To use these fields in formulas, specify the time zone ID "Etc/GMT" of Coordinated Universal Time. When you specify other time zones, you will not obtain the result as you expect.

For details on the ID for each time zone, see the following page:
Time Zone List (Users & System Administration Help)

When the time zone that uses daylight savings time is specified, daylight savings time is applied to the calculated result.
Example) DATE_FORMAT(date-time, "YYYY/MM/dd HH:mm ZZ", "America/Los_Angeles") works as follows:
  • 2019-01-01 00:00 in UTC is converted to 2018/12/31 07:00 - 08:00.
  • 2018-08-01 00:00 in UTC is converted to 2018/07/31 08:00 - 07:00.

Date and Time Format

Specify the date and time format by using the format specifiers listed in the following chart. Each format specifier is converted to the corresponding string.

Format Specifier Description String when 2020-4-1 PM 00:00 is converted
YYYY or yyyy Converts to a western calendar year. 2020
MMMM Converts to a month corresponding to the display language that is set by the user who edited the record. English: April
Japanese: 4月
Chinese: 四月
MMM Converts to a month in abbreviation corresponding to the display language that is set by the user who edited the record. English: Apr
Japanese: 4
Chinese: 四月
MM Converts to a month with zero-padding. 04
M Converts to a month without zero-padding. 4
dd Converts to a day with zero-padding. 01
d Converts to a day without zero-padding. 1
a Converts to "AM" or "PM" corresponding to the display language that is set by the user who edited the record. English: PM
Japanese: 午後
Chinese: 下午
KK Converts to the 12-hour clock display where 0 to 11 o'clock is considered as being before noon. Zero-padding is applied. 00
K Converts to the 12-hour clock display where 0 to 11 o'clock is considered as being before noon. Zero-padding is not applied. 0
hh Converts to the 12-hour clock display where 1 to 12 o'clock is considered as being before noon. Zero-padding is applied. 12
h Converts to the 12-hour clock display where 1 to 12 o'clock is considered as being before noon. Zero-padding is not applied. 12
HH Converts to the 24-hour clock display where one day refers to the time between 0 to 23 o'clock. Zero-padding is applied. 12
H Converts to the 24-hour clock display where one day refers to the time between 0 to 23 o'clock. Zero-padding is not applied. 12
kk Converts to the 24-hour clock display where one day refers to the time between 1 to 24 o'clock. Zero-padding is applied. 12
k Converts to the 24-hour clock display where one day refers to the time between 1 to 24 o'clock. Zero-padding is not applied. 12
mm Converts to the minute display with zero-padding. 00
m Converts to the minute display without zero-padding. 0
ss Converts to the second display with zero-padding. 00
s Converts to the second display without zero-padding. 0
Z Converts to the time difference from UTC (Coordinated Universal Time). The format is "hhmm". +0900
ZZ Converts to the time difference from UTC (Coordinated Universal Time). The format is "hh:mm". +09:00
ZZZ Converts to the time zone ID. Asia/Tokyo

Example Formula

This section explains example formulas that use the DATE_FORMAT format.

Formula: DATE_FORMAT(Field A, "YYYY-M-d", "Etc/GMT")

Changes the format of Field A to YYYY-M-d.
If Field A is "2020-04-01", the result is "2020-4-1".
Formula: DATE_FORMAT(Field A, "YYYY-MM-dd HH:mm", "America/Los_Angeles")

Changes the time zone of Field A to Pacific Standard Time.
If Field A is "2020-04-01 13:00", the result is "2020-03-31 21:00".
Formula: Field A & "-" & DATE_FORMAT(Created datetime, "YYYYMMdd", "system")

Concatenates the string in Field A and the created date and time of the record.
IF Field A is "Jennifer Anderson" and the created date and time of the record is "2020-04-01 13:00", the result is "Jennifer Anderson-20200401".