Converting Date to Text Format
Method 1: TEXT Function
First, use the DATE function to convert the value of the Date&Time field into a date object. Then, use the TEXT function to convert the date object into text in a specific format.
DATE: Converts the Date&Time field, whose return value is a timestamp, into a date object.TEXT: Converts a date object into text in a specific format.
Taking the date object 2024-10-08 09:13:34 as an example, the commonly used formats and conversion results are as follows:
Time Format | Description |
| Represents the year, displayed as |
| Represents the month, displayed as |
| Represents the day, displayed as |
| Represents the hour in 24-hour format, displayed as |
| Represents the minute, displayed as |
| Represents the second, displayed as |
Effect Demonstration
Convert to text in the “Year-Month-Day” format
The formula is set as follows, where yyyy represents the year, MM represents the month, and dd represents the day.
TEXT(DATE(Date&Time),'yyyy-MM-dd')
Convert to text in the “Year-Month-Day Hour:Minute:Second” format
The formula is set as follows, where HH represents the hour, mm represents the minute, and ss represents the second.
TEXT(DATE(Date&Time),'yyyy-MM-dd HH:mm:ss')
More Formats
You can freely combine text formats as needed. As shown below, the formula converts the Date&Time value into text in the “YearMonthDay” format. For more time formats, see TEXT(date, text_format).
TEXT(DATE(Date&Time),'yyyyMMdd')
Method 2: DATE Function
1. Configuration
Use date functions to separately extract information such as the year, month, and day from the Date&Time field. The extracted values are of the number type. Then, use the TEXT function to convert the numbers into text, and use CONCATENATE to join them into the required text format.
YEAR: Extracts the year from the Date&Time value.MONTH: Extracts the month from the Date&Time value.DAY: Extracts the day from the Date&Time value.HOUR: Extracts the hour from the Date&Time value.MINUTE: Extracts the minute from the Date&Time value.SECOND: Extracts the second from the Date&Time value.TEXT: Converts numbers into text format.CONCATENATE: Combines multiple text strings into one text string.
Effect Demonstration
Convert to text in the “Year-Month-Day” format
Extract the year, month, and day from the Date&Time value separately, convert them into text format, and then join them with -. The formula is set as follows:
CONCATENATE(TEXT(YEAR(DATE(Date&Time))),'-',TEXT(MONTH(DATE(Date&Time))),'-', TEXT(DAY(DATE(Date&Time))))
Convert to text in the “Year-Month-Day Hour:Minute:Second” format
Extract the year, month, day, hour, minute, and second from the Date&Time value separately, convert them into text format, and then join them with -, a space, and :.
CONCATENATE(TEXT(YEAR(Date&Time)),'-',TEXT(MONTH(Date&Time)),'-', TEXT(DAY(Date&Time)),'',TEXT(HOUR(Date&Time)),':',TEXT(MINUTE(Date&Time)),':',TEXT(SECOND(Date&Time)))
2. Supplement: About the Number of Text Digits
When using the TEXT function to convert the extracted number-type month, day, and hour into text, the number of digits may be missing. For example, when converting the month in the Date&Time value 2021-05-21, 05 can only be converted into the text 5.
Therefore, to unify the format as XXXX-XX-XX XX:XX:XX, you need to pad the month, day, hour, minute, and second with digits by adding a leading 0. The logic is as follows:
For the converted text-type month, day, hour, minute, and second, first prepend 0, and then use the RIGHT(a,b) function to take 2 characters from the right side of string a. Taking the month as an example, the formula and effect are as follows:
RIGHT('0'+TEXT(MONTH(DATE(Date&Time))),2)If the month is
12, after prepending0, it becomes012. Taking the rightmost 2 characters still returns12.If the month is
5, after prepending0, it becomes05. Taking the rightmost 2 characters returns05.
In this way, the extracted month is always in a 2-digit format. The same logic applies to other formats.
Effect Demonstration
Convert to text in the “Year-Month-Day” format
CONCATENATE(TEXT(YEAR(DATE(Date&Time))),'-',RIGHT('0'+TEXT(MONTH(DATE(Date&Time))),2),'-', RIGHT('0'+TEXT(DAY(DATE(Date&Time))),2))
Convert to text in the “Year-Month-Day Hour:Minute:Second” format
CONCATENATE(TEXT(YEAR(Date&Time)),'-',RIGHT('0'+TEXT(MONTH(Date&Time)),2),'-', RIGHT('0'+TEXT(DAY(Date&Time)),2),' ',RIGHT('0'+TEXT(HOUR(Date&Time)),2),':',RIGHT('0'+TEXT(MINUTE(Date&Time)),2),':',RIGHT('0'+TEXT(SECOND(Date&Time)),2))
Converting Text to Date Format
1）Implementation Logic and Required Functions
First, use MID to extract the year, month, day, and other information from the text separately, and convert the extracted text into number format. Then, use DATE to convert the values into Date&Time format.
MID: Extracts a specific number of characters from a text string, starting from a specified position.VALUE: Converts text into number format.DATE: Converts Date&Time values, or timestamps, into a date object.
2）Function Examples
Convert to Date&Time in the “Year-Month-Day” format
Extract the year, month, and day from the Single Line Text field separately, convert them into number format, and then convert them into Date&Time format.
DATE(VALUE(MID(Single Line Text,1,4)),VALUE(MID(Single Line Text,5,2)),VALUE(MID(Single Line Text,7,2)))
Convert to Date&Time in the “Year-Month-Day Hour:Minute:Second” format
Extract the year, month, day, hour, minute, and second from the Single Line Text field separately, convert them into number format, and then convert them into Date&Time format.
DATE(VALUE(MID(Single Line Text,1,4)),VALUE(MID(Single Line Text,5,2)),VALUE(MID(Single Line Text,7,2)),VALUE(MID(Single Line Text,10,2)),VALUE(MID(Single Line Text,13,2)),VALUE(MID(Single Line Text,16,2)))
