Many systems export dates as eight digits, such as 20261025, which Excel treats as a plain number or text. It cannot sort by date or calculate days between. Slicing out the year, month and day and rebuilding them with DATE produces a real Excel date.
20261025 becomes Sunday 10/25/2026, which falls in Q4.
The example
Three imported invoice dates. The second column shows the real date; the third shows its quarter.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Case | YYYYMMDD | Real date | Quarter |
| 2 | Invoice 1 | 20261025 | Sun 10/25/2026 | Q4 |
| 3 | Invoice 2 | 20260314 | Sat 3/14/2026 | Q1 |
| 4 | Invoice 3 | 20250731 | Thu 7/31/2025 | Q3 |
The formula
DATE needs three numbers; LEFT, MID and RIGHT supply them from the text:
How it works
Here is the logic, one piece at a time:
LEFT(B2,4)is the year.MID(B2,5,2)is the month, starting at character 5.RIGHT(B2,2)is the day.DATE(...)builds a true Excel date from the three parts.
Format the result column as a date; the underlying value is a serial number.
Try it: interactive demo
Type an 8-digit date such as 20261025.
Variations
Handle numbers and text
Works if the source is a true number.
Trap bad dates
Return a blank instead of an error.
Pitfalls & errors
If a date is not exactly 8 characters, MID and RIGHT slice the wrong characters. Check with LEN(B2)=8 first.
Excel silently rolls over invalid dates such as month 13. Validate the parts if your source is unreliable.
Store the result as a date and format it as you like; do not convert it back to text.
Practice workbook
Frequently asked questions
Why not use DATEVALUE?
Does this work for yyyymmdd numbers?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class