The basic principle of converting a date to a number is to first convert the date into text format, and then convert the text into number format.
Method 1: TEXT Function
This method applies when you want to convert 2021-03-15 into a number format such as 20210315. In other words, zeros in the date are displayed normally in the number.
Functions used:
TEXT(DATE(),"yyyyMMdd"): Converts the date into text format, whereyyyyrepresents the year,MMrepresents the month, andddrepresents the day.VALUE(): Converts text into number format.
The complete formula is as follows:
VALUE(TEXT(DATE(Date&Time),"yyyyMMdd"))
Effect:
Method 2: DATE Function
This method applies when you want to convert 2021-03-15 into a number format such as 2021315. In other words, zeros in the date are not displayed in the number.
Functions used:
DATE(): Converts the Date&Time value, or timestamp, into a date object.YEAR(DATE()): Extracts the year from the date object.MONTH(DATE()): Extracts the month from the date object.DAY(DATE()): Extracts the day from the date object.CONCATENATE(): Joins the extracted year, month, and day together.VALUE(): Converts text into number format.
The complete formula is as follows:
VALUE(CONCATENATE(YEAR(DATE(Date&Time)),MONTH(DATE(Date&Time)),DAY(DATE(Date&Time))))
Effect:
