How to Convert Number (YYYYMMDD) to Date Format in Excel – 4 Methods

Several Date formats are available in Excel.

Convert Number (YYYYMMDD) to Date Format in Excel


This is the sample dataset.

Dataset for Converting Date Format


Method 1 – Using the TEXT Function to Convert Number (YYYYMMDD) to Excel Date Format

Steps:

  • Enter the following formula in C5.
=TEXT(B5,"mm/dd/yyyy")

Use of TEXT Function Convert Number to Date Format in Excel

  • Press ENTER.

Formula Breakdown

  • The TEXT formula takes B5 as the first argument.
  • The second argument is format_text which contains the formatting (Here, “mm/dd/yyyy”) Other formats can be used.

Converting Number to Date Format with Excel TEXT Function

  • Drag the Fill Handle across the cells you want to fill.

Applying Fill Handle Icon

Dates will be displayed.

Copy the Formula with TEXT Function to all Columns.

Read More: How to Convert Number to Date in Excel


 Method 2 – Combining the DATE, RIGHT, LEFT & MID Functions to Convert Number (YYYYMMDD) to Date

Steps:

  • Enter the following formula in C5:
=DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,2))
  • Press ENTER and you will see the result for B5.

Combining DATE, LEFT, MID and RIGHT Functions

Formula Breakdown

  • The first argument for the LEFT, RIGHT, and MID functions is text.
  • Since yyyymmdd is the format of the dataset, the leftmost number up to 4 returns the year, middle 2 returns the month and the right 2 returns the date.
  • The functions contain numbers in the second argument. 4 for the LEFT formula, 2 for RIGHT, and 2 for MID.
  • The three functions are nested within the DATE function to return the result.
  • Drag the Fill Handle across the cells you want to fill to see the results.
  • Convert Number (YYYYMMDD) to Date Using Excel Functions

    Read More: How to Convert Text to Date in Excel


    Method 3 – Applying Text to Columns Wizard to Convert Number (YYYYMMDD) to Date Format

    Steps:

    • Select the dataset you want to convert.
    • Go to the Data tab.
    •  In Data Tools, select Text to Columns.

    Applying Text to Columns Feature to Convert Number (YYYYMMDD) to Date Format

    In the Text to Columns Wizard:

    • Choose Delimited and click Next in the Convert Text to Columns Wizard.

    Pressing NEXT Button to the Convert Text to Columns Wizard - Step 1 of 3

    • Keep all the tabs unselected and click Next in the Convert Text to Columns Wizard.

    Uncheck all in the Convert Text to Columns Wizard - Step 2 of 3

    • Select Date and from the drop-down menu select YMD >> select Destination >> click Finish in the Convert Text to Columns Wizard.

    Pressing FINISH Button to the Convert Text to Columns Wizard - Step 3 of 3

    You will see the result as shown below.

    Results for Using Text to Columns Feature

    Read More: How to Convert 8 Digit Number to Date in Excel


    Method 4 – Using VBA Macros to Convert Number (YYYYMMDD) to Date Format

    Steps:

    • Press ALT+F11. The VBA Macro will open.
    • In the Insert tab choose Module.

    Inserting Module

    • Enter the code below.
    Sub Converting_Num_yyyymmdd_To_Date()
    Dim my_numbr As Range
    Dim my_selected_range As Range
    On Error Resume Next
    xTitleId = "Converting Number to Date with Exceldemy"
    Set my_selected_range = Application.Selection
    Set my_selected_range = Application.InputBox("Range", xTitleId, my_selected_range.Address, Type:=8)
       For Each my_numbr In my_selected_range
           my_numbr.Value = DateSerial(Left(my_numbr.Value, 4), Mid(my_numbr.Value, 5, 2), Right(my_numbr.Value, 2))
           my_numbr.NumberFormat = "mm/dd/yyyy"
       Next
    End Sub

    VBA Code for Converting Number (YYYYMMDD) to Date Format in Excel

    Code Breakdown

    • A Sub Procedure named Converting_Num_yyyymmdd_To_Date was created.
    • Two variables my_numbr  and my_selected_range are declared as Range.
    • A dialog box was used to select the data.
    • A For Each Loop was used to apply the formula to every cell in the selection.

    • Save the file as Excel Macro-Enabled Workbook and go back to the worksheet.
    • In the Developer tab >> select Macros.

    Clicking Macros to Run Code

    • In the Macro dialog box choose Converting_Num_yyyymmdd_To_Date >> click Run.

    Run the Code

    • Select the range and click OK.

    Select Range to Convert Number (YYYYMMDD) to Date Format in Excel

    The result is displayed.

    Converted Number (YYYYMMDD) to Date Format with VBA Code


    Different Available Formats of Date in Excel

    Steps:

    • Select the data.
    • Press CTRL+1.

    Pressing CTRL+1 to Open Format Cells Dialog Box

    In Format Cells click Numbers and choose Dates.

    • Choose a date format.
    • Click OK.

    Available Types of Date in Format Cells Feature

    Alternatively,

    • In the  Home tab select Data and then Number  from the drop-down menu.
    • Choose More Number Formats to see different date formats.

    Available Number Formats in Excel

    The picture below showcases 3 different formats.

    Different Date Formats in Excel


    Stop Auto Conversion of Numbers to Dates

    Tips

    1. Use a space or an apostrophe before the number. The apostrophe is preferable as the space might hamper the efficiency of other Excel functions.

    2. Select the cells where you will enter data and press CTRL+1. Format Cells will open. Select Text and click OK.


    Practice Section

    Practice by yourself.

    Practice Section to Convert Number to Date Format in Excel


    Download Practice Workbook

    You can download the practice workbook here.


    Related Articles


    << Go Back to Convert to Date | Date-Time in Excel | Learn Excel

    Get FREE Advanced Excel Exercises with Solutions!
    Syeda Fahima Nazreen
    Syeda Fahima Nazreen

    SYEDA FAHIMA NAZREEN is an electrical & electronics engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Proteus, MATLAB, Multisim, AutoCAD, Jupiter Notebook, and MS Office, going beyond the basics. With a B.Sc in Electrical & Electronic Engineering from American International University, Bangladesh, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively... Read Full Bio

    2 Comments
    1. Your copy/paste formula in the 2nd method is incorrect:

      =DATE(LEFT(B5,4),MID(B5,3,2),RIGHT(B5,4))

      In your screenshot, it is right as:

      =DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,4))

      FYI.

      • Greetings LIBBY,
        Thank you for letting us know. We’ve updated it.
        Feel free to comment if you have further inquiries. We are here to help.
        Regards
        Arin Islam (Exceldemy Team)

    Leave a reply

    Advanced Excel Exercises with Solutions PDF

     

     

    ExcelDemy
    Logo