Function Problems

How to Convert a Date from DD/MM/YYYY to YYYYMMDD in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs to change standard date formats (such as 29/06/2024) into a continuous text string format (like 20240629) without slashes or separators.

Product: Excel
Device & OS: not provided
Scenario: Reformatting dates into a compact string format, often required for data exports, system integration, or specific database processing rules.
Observed behavior: Dates are currently in a standard DD/MM/YYYY format and need to be restructured into a YYYYMMDD format without altering the actual day represented.

Before you start

Ensure that your source dates are recognized by Excel as valid date values and not as plain text before attempting the conversion formula.

Solution 1Recommended

Use Formula to Convert Date to YYYYMMDD String

Combine the YEAR, MONTH, and DAY functions wrapped in TEXT functions to reliably extract and reformat the date components into a continuous string.

This method extracts the year, month, and day separately and forces them into specific digit lengths (four digits for year, two for month and day). It is highly reliable even if your computer's regional date settings differ.

Select the destination cell

Click on an empty cell where you want the newly formatted YYYYMMDD date to appear.

Enter the conversion formula

Type the following formula: =TEXT(YEAR(A2),"0000")&TEXT(MONTH(A2),"00")&TEXT(DAY(A2),"00") (assuming your original date is located in cell A2).

Apply and fill down

Press Enter to apply the formula. You can then click and drag the fill handle at the bottom-right corner of the cell to apply this conversion to the rest of your date column.

Formula Result Verification: If the result is incorrect or displays a #VALUE! error, verify that Excel recognizes the source cell as a date rather than plain text. You may need to use the 'Text to Columns' tool to fix text-based dates.

Solution 2

Change Date Display Using Custom Formatting

Change how the date is displayed visually in the cell without altering the underlying date serial number.

Seamless Data Processing

Convert and Format Dates Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful functions and intuitive formatting tools to handle all your date conversion needs quickly, acting as a lightweight and highly efficient alternative.

1. Open data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates.
2. Apply formula or formatting: Select a cell and input =TEXT(A2, "yyyymmdd") for a quick text conversion, or use Ctrl+1 to open custom formatting.
3. Drag to batch convert: Use the fill handle to drag down and instantly convert thousands of rows.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx formats.Effortlessly convert date formats using custom formatting or the TEXT function.Free and lightweight office suite with a familiar, easy-to-navigate user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #VALUE! error when converting dates?

This usually happens if the original date in your cell is stored as text rather than a valid date. To fix this, select the column with your dates, go to the Data tab, click 'Text to Columns', and immediately click 'Finish' to force Excel to recognize them as dates.

Can I use the TEXT function directly without the YEAR, MONTH, and DAY functions?

Yes. If your system's regional settings align with the date input and the source cell is a perfectly valid date, you can often use a simpler formula like =TEXT(A2, "yyyymmdd") to achieve the exact same compact string result.

Will changing the cell format to yyyymmdd change the actual cell data?

No. Using custom cell formatting (via Ctrl+1) only changes how the date is displayed visually on the screen. The underlying data remains a standard date serial number. If you need the actual underlying value to be a text string for a system upload, you must use the TEXT formula method instead.

Excel & Data Problems