Forum Discussion
How to use a string of numbers entered into a cell and it reflects mm/dd/yyyy.
If you mean typing 09182026 and getting 09/18/2026, simply applying a Date format will not work. Excel treats a plain number as a date serial number, not as month, day and year.
Assuming your numbers are in MMDDYYYY order:
- Put the original number in A1.
- In B1, enter:=LET(s,TEXT(A1,"00000000"),DATE(VALUE(RIGHT(s,4)),VALUE(LEFT(s,2)),VALUE(MID(s,3,2))))
- Select B1, press Ctrl + 1, choose Custom, and enter mm/dd/yyyy.
This creates a real Excel date, so you can sort it and use it in calculations. It also handles a missing leading zero, such as 9182026.
If you only want the slashes to appear in the cell where you type, use the custom number format 00"/"00"/"0000" instead. That changes the display only; it does not create a real date or validate the month and day.
The formula assumes valid dates and MMDDYYYY order. If your numbers look like 20260918 instead, the conversion needs a different arrangement.
If you type 09182026 into a cell and want it to show as 09/18/2026, applying a Date format alone will not work. Excel treats a plain number as a date serial number, not as month/day/year — so 09182026 is stored as the number 9,182,026 and would render as a nonsense date far past year 9999.
Assuming your numbers are in MMDDYYYY order:
To create a real date (sortable, usable in calculations):
- Put the original number in A1.
- In B1, enter:
=LET(s,TEXT(A1,"00000000"),DATE(RIGHT(s,4),LEFT(s,2),MID(s,3,2)))
Select B1, press Ctrl + 1 → Custom → enter mm/dd/yyyy.
The TEXT(...,"00000000") part pads the value to 8 digits, so it also handles a missing leading zero like 9182026.
If you only want the slashes to appear in the same cell (display only, not a real date):
Select the cell → Ctrl + 1 → Custom → enter:
00"/"00"/"0000"
This just changes how the number looks. It does not create a real date, and it does not validate the month or day.
Two important caveats:
- Invalid dates roll over silently. DATE(2026,13,45) doesn't error — Excel rolls month 13 into the next year and day 45 into the following month. So a bad entry like 13152026 would produce a valid-looking but wrong date. If you need to catch that, use:
=LET(s,TEXT(A1,"00000000"),m,VALUE(LEFT(s,2)),d,VALUE(MID(s,3,2)),y=VALUE(RIGHT(s,4)),
IF(OR(m<1,m>12,d<1,d>DAY(EOMONTH(DATE(y,m,1),0))),"Invalid",DATE(y,m,d)))
- Different order? If your numbers are YYYYMMDD (e.g. 20260918), swap the pieces:
=LET(s,TEXT(A1,"00000000"),DATE(LEFT(s,4),MID(s,5,2),RIGHT(s,2)))
