Converting Between Text and Date Formats

Introduction

You can use formulas to convert between text and date formats.

Converting Date to Text Format

Using the TEXT Function

You need to use the following functions:

Set the Formula as follows:

TEXT(DATE(Date&Time),'yyyy-MM-dd')

The DATE function is used to convert the date value in the Date&Time field into a date serial number. This number is also the first argument of the TEXT function. The second argument of the TEXT function specifies the desired format for the output text string, which is "yyyy-MM-dd". This format specifies that the year should be represented by four digits, the month by two digits, and the day by two digits, separated by hyphens.

You can modify the text format according to your needs, for example:

TEXT(DATE(Date&Time),'yyyyMMdd')

This means the formula will convert a date value to a text string in the format of "yyyyMMdd".

Using the DATE Function

You need to use the following functions:

Set the Formula as follows:

CONCATENATE(TEXT(YEAR(DATE(Date&Time))),'-',TEXT(MONTH(DATE(Date&Time))),'-',TEXT(DAY(DATE(Date&Time))))

The DATE function is used to convert the date value in the Date&Time field. The resulting date serial number is then passed as the argument to the YEAR, MONTH, and DAY functions, which extract the year, month, and day values of the date respectively. The extracted values are passed to the TEXT function, which converts them into a text string with a specific format. The resulting text strings are concatenated together using the CONCATENATE function, with the hyphens "-" as separators.

Note that the text string is in the format "2023-7-4", with one digit for the month and date values. If you want to return a text in the format of "yyyy-MM-dd", you can follow the steps below to modify it.

Formatting Month and Day Values as Two-Digit Strings

To pad the month and day values with a leading zero, you need to first convert them to a text format before concatenating the values with a "0". This is because using the "+" sign to concatenate the values would result in a mathematical addition instead of a string concatenation.

The year value in the field is always a four-digit number, so you do not need to modify the year value.

The month and day values may have either one or two digits, so you need to pad them with a leading zero and ensure that they are always represented by two digits. The RIGHT function can be used to extract the last two digits of the padded value. To pad the month value with a leading zero, you can use the following formula:

RIGHT('0'+TEXT(MONTH(DATE(Date&Time))),2)

This formula adds a "0" to the beginning of the month value, then extracts the last two digits of the resulting string using the RIGHT function. The same approach can be used to pad the day value.

Therefore, the final formula is:

CONCATENATE(TEXT(YEAR(DATE(Date&Time))),'-',RIGHT('0'+TEXT(MONTH(DATE(Date&Time))),2),'-',RIGHT('0'+TEXT(DAY(DATE(Date&Time))),2))

The effect is as follows:

Converting Text to Date Format

You need to use the following functions:

If the text format is yyyyMMdd, you can convert it to a date value by setting the following formula:

DATE(VALUE(MID(Single Line,1,4)),VALUE(MID(Single Line,5,2)),VALUE(MID(Single Line,7,2)))

The MID function extracts the year, month, and day values from the Single Line field. The VALUE function converts them to numerical values. These values are then used as arguments for the DATE function, which returns a Date&Time format.