You can use the following formula to convert a date to a YYYYMMDD format in Excel:
=TEXT(A1, "YYYYMMDD")
This will convert a date such as 1/4/2022 to a format of 20220104.
You can also insert dashes between the year, month and day:
=TEXT(A1, "YYYY-MM-DD")
This will convert a date such as 1/4/2022 to a format of 2022-01-04.
Note that each formula assumes the date is in cell A1.
The following example shows how to use each formula in practice.
Example: Convert Date to YYYYMMDD Format in Excel
We can use the following formula to convert a date to a YYYYMMDD format in Excel:
=TEXT(A2, "YYYYMMDD")
We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:
Column B displays each date in column A in a YYYYMMDD format.
For example:
- 1/4/2022 has been converted to 20220104.
- 1/6/2022 has been converted to 20220106.
- 2/3/2022 has been converted to 20220203.
And so on.
We can also insert dashes between the year, month and day by using the following formula:
=TEXT(A2, "YYYY-MM-DD")
We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:
Column B displays each date in column A in a YYYY-MM-DD format.
For example:
- 1/4/2022 has been converted to 2022-01-04.
- 1/6/2022 has been converted to 2022-01-06.
- 2/3/2022 has been converted to 2022-02-03.
And so on.
Note: You can find the complete documentation for the Excel TEXT function here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Convert Date to Quarter and Year in Excel
How to Compare Dates Without Time in Excel
How to Filter Dates by Month in Excel