# How to convert YYYYMMDD to a date in Excel

Cut out the year, month and day and rebuild the date: `=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))`.

**Excel:** `=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Raw | Date |
| 20260305 |  |
| 20251231 |  |
| 20260110 |  |
| 20260228 |  |
| 20261001 |  |

Formula in B2, filled to B6: `=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))`

Result: 3/5/2026; 12/31/2025; 1/10/2026; 2/28/2026; 10/1/2026

## How it works

LEFT, MID and RIGHT cut the year, month and day out of the eight digits, and DATE puts them together as a real date. They work on numbers too, because the text functions turn the number into text first.

## Other ways

- `=DATE(INT(A2/10000),MOD(INT(A2/100),100),MOD(A2,100))`: Does the same with arithmetic instead of text functions.

## Common mistakes

- A date format on 20260305 doesn't work: it is read as day number 20,260,305, beyond the year 9999, and shows as ####.
- If the column has blanks, wrap it: `=IF(A2="","",DATE(…))`, or the blank rows show #VALUE!.
- DATE quietly rolls invalid dates forward: 20260231 becomes March 3, 2026. Check suspicious source values.

## Questions

**How do I convert the date back to YYYYMMDD?**

`=TEXT(B2,"yyyymmdd")` gives text; wrap it in `--` for a number.

**What about DDMMYYYY numbers?**

Pad them to 8 digits first, since a leading zero is lost: `=DATE(RIGHT(A2,4),MID(TEXT(A2,"00000000"),3,2),LEFT(TEXT(A2,"00000000"),2))`.

**What if the value includes a time, like 202603140930?**

Add the time from the last four digits: `=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(MID(A2,9,2),RIGHT(A2,2),0)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-yyyymmdd-to-a-date