How to Convert 8-Digit Number to Date in Excel

To convert an 8-digit number such as 20240115 (YYYYMMDD) into a real Excel date, use the formula =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), which pulls the year, month, and day out of the number and rebuilds them as a date serial. You can also do it without a formula using Data → Text to Columns and choosing the Date (YMD) option in the final step.

Are you struggling to convert an 8-digit number to date format in Excel? This task may seem daunting at first, but with the right approach, it can be achieved quickly and accurately. In this guide we walk through every reliable method — formulas, Text to Columns, and DATEVALUE — so you can convert 20240115 to January 15, 2024 no matter which layout your numbers use. Whether you’re a beginner or an experienced Excel user, these instructions will make the task easy to accomplish.

Understand the Format of the 8-Digit Number

Before we begin, it’s important to understand the format of the 8-digit number. In Excel, dates are stored as serial numbers starting from January 1, 1900, with January 1, 2021, being represented by the serial number 44197. This means an 8-digit number is not a date — it is just a number that looks like one, and Excel will not treat it as a date until you convert it.

The most common 8-digit layout is YYYYMMDD, where 20240115 means the year 2024, month 01, and day 15. Other layouts exist too — 01152024 is MMDDYYYY and 15012024 is DDMMYYYY — and the only difference between the methods below is which four digits you treat as the year. For a refresher on how Excel stores and manipulates dates, see our Excel date and time complete guide and the Excel date functions cheat sheet.

Convert YYYYMMDD with the DATE Function (Recommended)

For an 8-digit number in YYYYMMDD format such as 20240115, the cleanest method is the DATE function combined with the LEFT, MID, and RIGHT text functions. Assuming your number is in cell A1:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Here is what each piece does:

• LEFT(A1,4) grabs the first four digits (2024) and uses them as the year.
• MID(A1,5,2) grabs the two digits starting at position 5 (01) and uses them as the month.
• RIGHT(A1,2) grabs the last two digits (15) and uses them as the day.

DATE then assembles those three values into a proper date serial number. The result will display as a real Excel date that you can sort, filter, and use in calculations such as counting the days between two dates. These text-extraction functions are covered in more depth in our Excel text functions overview.

Note: DATE returns a serial number, so the cell may initially show something like 45306 instead of a date. That is expected — see Formatting the Result as a Date below to fix the display.

Convert with Text to Columns → Date (YMD)

If you prefer not to use a formula, Excel’s Text to Columns wizard can convert a column of YYYYMMDD numbers to dates in place:

1. Select the column containing your 8-digit numbers.
2. Go to the Data tab and click Text to Columns.
3. In Step 1, choose Delimited (the choice does not matter for a single column) and click Next.
4. In Step 2, leave all delimiters unchecked and click Next.
5. In Step 3, under Column data format, select Date and choose YMD from the dropdown to match the YYYYMMDD layout.
6. Click Finish.

Excel converts each value to a real date automatically. If your numbers are in MMDDYYYY format, pick MDY instead; for DDMMYYYY, pick DMY. This method is fast for one-time cleanups and leaves no formula behind.

Convert with the DATEVALUE / TEXT Approach

You can also build a date by first turning the number into a formatted text string and then converting that text to a date with DATEVALUE. For a YYYYMMDD number in A2:

=DATEVALUE(TEXT(A2,"0000-00-00"))

The inner TEXT(A2,"0000-00-00") reshapes 20240115 into the text string 2024-01-15, and DATEVALUE parses that recognizable date text into a serial number. This two-step approach is handy when your data is already text, or when you want to standardize the layout before converting.

The original two-formula version of this method works as well. First convert the number to text:

=TEXT(A2,"0000-00-00")

Then convert that text to a date serial in the next cell:

=DATEVALUE(B2)

Either form produces the same result. DATEVALUE only accepts text, so if you ever pass it a number directly you will get an error — see the troubleshooting note below.

Formatting the Result as a Date

Because DATE and DATEVALUE both return serial numbers, your converted cells may show a number like 45306 instead of a readable date. To fix the display:

1. Select the cells in the new column.
2. Press Ctrl + 1 (or right-click and choose Format Cells).
3. On the Number tab, select the Date category.
4. Pick your preferred format — Short Date, Long Date, or a custom format such as yyyy-mm-dd.

The underlying value does not change; only how Excel displays it does. For a full walkthrough of the dialog and custom format codes, see how to format cells in Excel.

Handling Other Digit Layouts (MMDDYYYY and DDMMYYYY)

The DATE formula adapts to any 8-digit layout — you simply change which digits feed the year, month, and day arguments:

• YYYYMMDD (20240115): =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
• MMDDYYYY (01152024): =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))
• DDMMYYYY (15012024): =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))

In each case RIGHT(A1,4) or LEFT(A1,4) captures the four-digit year, and the remaining two MID/LEFT/RIGHT pieces map to month and day in the order your data uses. If you are unsure of the layout, look at a value where the day clearly exceeds 12 (for example, ...25) to identify which two digits are the day.

Tips for Handling Date Formats in Excel

Working with dates in Excel can be tricky, but there are a few tips and tricks you can use to make the process smoother:

1. Ensure Consistent Date Formats

Make sure that all dates in your Excel sheet are stored in a consistent date format. This will help prevent confusion and ensure that calculations and functions work correctly.

2. Use the DATE Function for Complex Calculations

If you need to perform complex calculations with dates, use the DATE function rather than formatting a cell to display a date in your preferred format. This will ensure that your calculations are accurate.

3. Use Data Validation to Avoid Input Errors

Use data validation to limit the date values that users can enter into your worksheet. This will help prevent input errors and ensure that all dates are stored consistently.

Troubleshooting

The result shows a serial number instead of a date. This is the most common issue. DATE and DATEVALUE return Excel date serial numbers, and a freshly filled cell may keep its General or Number format. Reformat the cell as a date using the steps in Formatting the Result as a Date.

You get a #VALUE! error. This usually means a function received the wrong type of input. DATEVALUE only accepts text — passing it a number directly triggers #VALUE!, so wrap the number in TEXT(...) first. A #VALUE! can also appear if the source cell contains stray spaces, non-numeric characters, or fewer than 8 digits (for example, a date that lost its leading zero). Confirm the cell holds exactly 8 digits and trim any spaces before converting.

A #NUM! error appears. This means the extracted month or day is out of range — for instance, the digits were mapped in the wrong order so the “month” came out as 15. Double-check that your LEFT/MID/RIGHT arguments match the layout described in Handling Other Digit Layouts.

Frequently Asked Questions

How do I convert 20240115 to a date in Excel?

Use =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), where A1 holds the number 20240115. The formula reads the first four digits as the year (2024), the middle two as the month (01), and the last two as the day (15), producing January 15, 2024. If the result shows a serial number, reformat the cell as a date.

How do I convert YYYYMMDD to a date in Excel without a formula?

Select the column, go to Data → Text to Columns, click through to Step 3, choose Date as the column data format, and select YMD from the dropdown. Click Finish and Excel converts every YYYYMMDD value to a real date in place.

What if my 8-digit number is in MMDDYYYY format?

Use =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)). This takes the last four digits as the year, the first two as the month, and the middle two as the day. With Text to Columns, choose the MDY date option instead of YMD.

Why does my converted cell show a number like 45306 instead of a date?

Excel stores dates as serial numbers, and your cell is still formatted as a number. Select the cell, press Ctrl + 1, choose the Date category, and pick a format. See how to format cells in Excel for details.

Why am I getting a #VALUE! error?

The DATEVALUE function only accepts text, so giving it a raw number returns #VALUE!. Wrap the number in TEXT(A2,"0000-00-00") first, or use the DATE method instead. A #VALUE! error can also come from extra spaces or a number that does not contain exactly 8 digits.

How do I calculate the number of days between two converted dates?

Once both values are real dates, subtract one from the other — for example, =A2-A1. You can also use dedicated date math; see how to calculate days between dates in Excel.