Forum Discussion

equinest's avatar
equinest
Occasional Reader
Sep 17, 2026

How to use a string of numbers entered into a cell and it reflects mm/dd/yyyy.

Need help with this.

excel

3 Replies

  • Terio's avatar
    Terio
    Brass Contributor
    Sep 18, 2026

    Use a custom format
    00/00/0000

    and manage the results with functions.

    Bye

  • Eric_Brooks's avatar
    Eric_Brooks
    Brass Contributor
    Sep 17, 2026

    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:

    1. Put the original number in A1.
    2. In B1, enter:=LET(s,TEXT(A1,"00000000"),DATE(VALUE(RIGHT(s,4)),VALUE(LEFT(s,2)),VALUE(MID(s,3,2))))
    3. 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.

    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Sep 18, 2026

      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):

      1. Put the original number in A1.
      2. 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)))