DATE_FORMAT Function: Usage Example

By using the DATE_FORMAT function in a formula, date, date & time, and time can be treated as strings.
If you specify a field code ""Report creation date" & date" without using DATE_FORMAT function, result value is "Report creation date 1575504000", which cannot be read as a date.
When the date and time format is not specified, the date and time is displayed in numeric values called "UNIX time".

If you use the DATE_FORMAT function and specify ""Report creation date" & DATE_FORMAT(Date, "MM-dd", "Etc/GMT")", the date is displayed as "Report creation date 12-05".

Example 1: Concatenating Date and String

This section describes the way to concatenate the DATE_FORMAT function with a string, by using an example of the Operating Report app.
Image of the completed app:

  1. Open the app settings page.
    Opening App Settings Page

  2. Select the Date field and the Created By field in the parts list, and place them in the form.

  3. Place the Text field for displaying a title.
    Make the field width longer so that title is displayed in one line.

  4. Open the screen to set the Text field, and change the field name to "Title".
    Keep the setting screen open.

  5. Select "Calculate automatically", and enter the formula.

    • Formula:
      "Report creation date" & " " & DATE_FORMAT(Date, "MM-dd", "Etc/GMT") & " " & Created by
  6. Click Save on the field settings screen.

  7. Click Save Form on the upper left side of the screen, and then click Update App on the upper right side of the screen.

When you create a record in the Operating Report app, the report creation date you specified using the DATE_FORMAT function is displayed with the values of "Date" and "Created by".

If you set the Title field as a title of a record, it will be displayed as a header title of a notification. You can also display it in the list of records. See "Setting the Record Title".
See "Configuring Views".

This is also useful when you want to sort records by dates or by user names (in a "Task Management" app, etc.).

Example 2: Using the DATE_FORMAT Function within the IF Function

You can specify the DATE_FORMAT function within the IF function.

Example Formula
IF(Display format="Japanese", DATE_FORMAT(Date, "yyyy/MM/dd", "Etc/GMT"), IF(Display format="American", DATE_FORMAT(Date, "MM/dd/yyyy", "Etc/GMT"), DATE_FORMAT(Date, "dd/MM/yyyy", "Etc/GMT")))

The date will automatically be displayed in Japanese format if you select "Japanese", US format if you select "American", or British format if you select "British".

For details on the IF function, see the "IF Function: Changing the Value to Display Depending on the Condition" page.

This section describes the way to display dates for each countries, using the combination of the IF function and the DATE_FORMAT function in a minutes management app.

Image of the completed app:

  1. Open the app settings page.
    Opening App Settings Page

  2. Select the Date field in the parts list, and place it in the form.
    Leave the default field code (Date) as is.

  3. Select the Text field and the Radio Button field in the parts list, and place them in the form.

  4. Open the screen to set the Radio Button field, and change the field name and the field code to "Display format".
    Set "Japanese", "American", and "British" as options.

  5. Click Save on the screen to set a radio button.

  6. Open the screen to set the Text field, and enter "Date" as the field name.

  7. Select "Calculate automatically", and enter the formula.

    • Formula to change the date display according to the country selected
      IF(Display format="Japanese", DATE_FORMAT(Date, "yyyy/MM/dd", "Etc/GMT"), IF(Display format="American", DATE_FORMAT(Date, "MM/dd/yyyy", "Etc/GMT"), DATE_FORMAT(Date, "dd/MM/yyyy", "Etc/GMT")))
  8. Click Save on the screen to set the Text field.

  9. Click Save Form on the upper left side of the screen, and then click Update App on the upper right side of the screen.

Now, the date is displayed using the date format selected.

TIPS: If Date or Time of Calculated Result Is Off

If the date and/or the time in the calculated result of DATE_FORMAT function is different from what they should look, it is possible that the time zone is not set properly.
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 example, if you set "Osaka, Sapporo, Tokyo (Asia/Tokyo)" for a time zone, the time shown would be +9 hours.

  • Time Zone Calculation - Wrong:
    DATE_FORMAT(Field A, "HH:mm", "Asia/Tokyo")
    If the value in Field A is "9:00", the calculated result is: 18:00

By specifying UTC Time Zone ID "Etc/GMT", the correct time will be displayed.

  • Time Zone Calculation - Correct
    DATE_FORMAT(Field A, "HH:mm", "Etc/GMT")
    If the value in Field A is "9:00", the calculated result is: 09:00