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 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.
Thank You.