ExcelInsider Team
In Excel, sometimes we take date input as a number. But later we need to convert those numbers or text strings into a date like YYYYMMDD format. That is because Excel only recognizes these YYYYMMDD as an actual date value. This situation happens when data is imported from databases, reports, or systems that store dates as numbers without delimiters. It makes them hard to manipulate or format as proper Excel dates. There we need the conversion.
To Convert Number to Date YYYYMMDD, follow these steps:
➤ Select the column that contains date as numbers or text
➤ Use a formula or Excel’s Text to Columns feature to parse and convert the data into YYYYMMDD format date.
In this article, we’ll see multiple methods to convert numbers in YYYYMMDD format into Excel recognized dates. We will use function, formulas, Excel features for different scenarios and data types.
What Are Date and Time Formats in Excel?
In Excel, date and time formats basically determine how date and time values are displayed in cells. While Excel stores dates as serial numbers (counting days from a base date) and times as fractions of a day, the format controls how these numbers appear visually to you. If date and time are not properly formatted we will face many problems while dealing with the dataset.
- Short Date: It displays the date as MM/DD/YYYY or DD/MM/YYYY. It may vary on regional settings (e.g., 5/17/2024 or 17/05/2024). Or
- Long Date: It displays the full date including the weekday (e.g., Friday, May 17, 2024).
- Year-Month-Day (YYYYMMDD): It is a common international format (e.g., 2024-05-17).
- Custom Formats: You can create custom date formats like:
- dd-mm-yyyy → 17-05-2024
- mmm dd, yyyy → May 17, 2024
- dddd, mmmm dd, yyyy → Friday, May 17, 2024
Using Excel Formula DATE + LEFT/MID/RIGHT
We will use Excel’s DATE, LEFT, MID, and RIGHT functions to convert a number or text (like 20240105) into an actual Excel date. It’s good when we import date data from external systems and store without separators.
Steps:
➤ Ensure the raw text date values are in a column (e.g., Column C).
➤ In the cell next to the first entry (e.g., D2), enter the following formula:
=DATE(LEFT(C2,4), MID(C2,5,2), RIGHT(C2,2))
This formula will extract the year, month, and day and use them to build a valid date.
➤ Use the fill handle (bottom right corner of the cell) to drag the formula down to other rows.
Note:
➧ Make sure that the source cell values are treated as text or plain numbers. If Excel auto converts them to dates incorrectly (like02E+07), format them as text first.
➧ You can nest VALUE() functions if necessary to coerce text into numeric values before applying DATE().
Apply Text to Columns Wizard
The Text to Columns Wizard is a simple, formula free method in Excel. It can reliably convert numbers or text strings in YYYYMMDD format into valid Excel date values. It is Ideal for many situations where bulk data has been imported (e.g., from databases, ERPs, or CSV files), and Excel is not recognizing the date format.
Steps:
➤ Click on the column that contains the 8 digit YYYYMMDD values (e.g., Column C).
➤ Go to the Excel Data tab and click Text to Columns.
➤ Select Delimited (even though we won’t actually use delimiters) and click Next.
➤ Make sure that all delimiter options (Tab, Comma, etc.) are unchecked, then click Next.
➤ Under “Column data format,” select Date, and in the dropdown beside it, select YMD to match the source format.
➤ Click Finish to convert the text to proper Excel date values.
Note:
➧ This method works regardless of whether the original data is stored as text or numeric values (as long as it’s an 8 digit YYYYMMDD structure).
➧ Excel will recognize the format whatever system locale or region settings because YMD is selected.
➧ Once converted, you can format the column further using “Format Cells” for custom display.
Implementing Custom Number Format to Display Date Value as YYYYMMDD
Excel stores dates as sequential numbers starting from January 1, 1900, which is considered serial number 1. Each day after that adds 1 to the serial number. So:
- January 1, 1900 = 1
- January 2, 1900 = 2
- January 1, 2024 = 45291
- May 18, 2025 = 46094
We will now use a method that will format valid date values in Excel to appear in YYYYMMDD format without altering the actual date. It’s useful when you want to preserve Excel’s native date functionality even though displaying dates in a standard numerical format : like for exports or reports.
Steps:
➤ Ensure that your column (e.g., Column C) contains actual date values, not raw yyyymmdd numbers.
➤ Highlight the cells you want to reformat (e.g., C2:C11).
➤ Right-click the selected range → Click Format Cells.
➤ In the Format Cells dialog, go to the-
- Number tab → Select Custom.
- In the Type field, enter: yyyy/mm/dd
- Click OK.
➤ Dates will now display as 2024/01/03, 2024/02/17, etc., while still being true date values in Excel.
Note:
➧ This method does not convert raw yyyymmdd values like 20240103 into date values. It only changes how existing date values are displayed.
➧ You can still use date functions like =TODAY()-C2 or =MONTH(C2) on these formatted cells.
➧ This formatting is for visual purposes and is not suitable for text or numeric date conversion from raw yyyymmdd.
Frequently Asked Questions (FAQs)
How do I convert numbers to dates in Excel?
You can convert numbers by using formulas like
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
or by using Excel’s Text to Columns feature.
How to convert datevalue to date in Excel?
Use =DATEVALUE(text_date) to convert text dates to Excel date. However, it will require recognizable date formats with delimiters.
How to convert a Number to date?
If your number represents an Excel serial date, you can simply format the cell as a Date.
Why does Excel convert dates to numbers?
Excel stores dates as serial numbers for calculation purposes. Formatting only can convert these numbers into readable dates.
Concluding Words
So you have read through the whole article with 3 easy methods that can convert numbers into YYYYMMDD format. This format in Excel dates is an important step for anyone who is up to data imports, reports, or any other scenario. Basically where dates are stored as plain numbers. If you face any errors or need any assistance let me know in the comment section below and I will reply to you asap.
