How to Convert 8 Digit Number to a Date in Excel [6 Ways]

You want to convert 8 digits text representing date to Date in Excel for your project. This article explains how to use Excel commands, functions, power pivot, power query, VBA, and Office Scripts to convert 8 digits to date.

Excel stores the date using serial numbers, ddddd.tttttt format. The number of days counting from January 1, 1900. You want to make sure the Date columns are in the data properly formatted as Date Format. If the data is not formatted as Date and given as an 8 digits number or text, you have to convert it.

You can perform arithmetic operations such as addition or subtraction with date. Also generate the Yearly, Quarterly and Monthly reports with formatted dates in Excel.

Most of the time, you can see the date are stored as text with or without the symbols / or –, to separate Year, Month, and Day in your data file. You will learn a few different methods to Convert 8 digits numbers stored as text to date in Excel.

This blog post covers how to Convert 8 Digits to Date in Excel using the Excel command Text to Columns, excel TEXT function, the combination of LEFT, MID, RIGHT, and DATE functions. Also, Explore the Power Query, and Power Pivot tools to convert 8 digits text to date. VBA or Office Scripts code helps you to convert text to a date format in your automation or dashboard.

If you receive the sales data table with the field Invoice Date, stored the 8 digits number as text. First, you want to properly format the Invoice Date in column B. Once formatted, you can simply create reports based on this column.

Get your copy of the example workbook used in this blog post to follow along!

Convert 8 digits to Date in Excel with Text to Columns

Excel Text to Column in Data Tab is splitting text data into separate columns. You can use this command to convert the 8 digits numbers or text data to date.

Please follow the below steps to convert 8 digits to Date

1. Select the range of cells B2:B16
2. Go to Data Tab, and Click the Text to Columns – to open Convert Text to Columns Wizard

3. Press Next in Step 1 of 3 in the wizard.

4. Press Next in Step 2 of 3 in the wizard

5. Select the Date option in the Column date format, Choose the YMD item in the dropdown list, and Press Finish.

You can see the properly formatted date in column B.

Convert 8 digits to Date in Excel with Text Function

TEXT function is used to convert numbers to text, format text, change the case of text, extract specific words from a text string and more. It will be useful to combine both numbers and text or symbols. This section explores how to use the TEXT function to convert text to date.

TEXT( value, format_text )

TEXT function format the given value

Go to cell E2, type the formula =TEXT( B2, "####-##-##" ), and Press Enter. Drag the fill handle to the range E2:E16 to copy the formula.

Convert 8 digits to Date in Excel with DATE function

This section explores how to use RIGHT, LEFT, and MID functions along withthe DATE function to convert 8 digits to date in Excel. The RIGHT function extracts the character from the last character. You have to pass three arguments text, starting position, and number of characters to the MID function. The LEFT function extracts the character from the left side.

RIGHT(text,[num_chars])

RIGHT returns the number of characters from the last character

LEFT(text,[num_chars])

LEFT returns the number of characters from the first character

MID(text,start_num,num_chars)

MID returns a number of characters from a starting position you specify

DATE( year, month, day )

It returns the Excel date-time serial number which represents a date.

Go to cell E2, type the formula =DATE( LEFT( B2, 4 ), MID( B2, 5, 2 ), RIGHT( B2, 2 ) ), and Press Enter. Drag the fill handle to the range E2:E16 to copy the formula.

In the above formula, the LEFT function returns the first four characters Year, the MID function returns the 5^th and 6^th character Month, and the RIGHT function returns the last two characters Day.

Convert 8 digits to Date in Excel with Power Pivot

You can use the Power Pivot add-in if you are dealing with a large data set or multiple data sources and need to convert 8 digits to date.  It enables you to create complex data models in Excel.

1. Select the range of cells A1:D16 in the Data Sheet
2. Go to Power Pivot Menu, and press the command Add to Data Model to add the data to the Power Pivot

3. Rename the table name PPData in the Power Pivot editor, select the Add column, type the formula =DATE( LEFT( PPData[Invoice Date], 4 ), MID( PPData[Invoice Date], 5, 2 ), RIGHT( PPData[Invoice Date], 2 ) ), rename the column header Formatted Date and press Enter.

4. Select the Flattened PivotTable option in the PivotTable command.

5. You have options to select the New Worksheet or Existing worksheet and select the worksheet location.  Once you press the OK button, Excel adds an empty pivot table in the worksheet.

6. You drag the fields Invoice No, Counter No, and the newly added column Formatted Date to the Rows Area.

Convert 8 digits to Date in Excel with Power Query

In this section, you learn how can use Power Query to convert 8 digits to date. It is a data processing tool to Extract, Transform, and Load data to Excel. Also enables you to connect multiple data sources, and format & clean the data.

Steps to convert text to numbers using Power Query

1. Select the range of cells A1:D16 in the Data Sheet
2. Choose From Table/Range option in Data Tab – to open the Power Query window.
3. Rename the Query name PQData

4. Click the Data type icon in the Invoice Date column
5. Select the Date.

6. Select the Close & Load command and Press Close & Load To… – to popup Import Data dialog box.

7. In the Import Data dialog box, select the Table option, and provide the location in the Existing worksheet to insert the transformed Power Query table into your worksheet.

Convert 8 digits to Date in Excel with VBA

VBA can be used to record repetitive tasks, create custom functions & formulas, automate processes, and create custom user interfaces. In this section you use a combination of Excel VBA functions RIGHT, LEFT and MID to split the text and create a date with the help of the DateSerial function.

Sub ConvertToDate()

' Declare variables
Dim rge As Range
Dim yy As Integer
Dim mm As Integer
Dim dd As Integer

' Assign values
Set rge = Sheets("VBA ").Range("B2:B16")

' Loop through each cell in the range
For Each rg In rge

    ' Assign Year, Month and Day to the respective variables yy, mm and dd
    yy = Left(rg.Value, 4)
    mm = Mid(rg.Value, 5, 2)
    dd = Right(rg.Value, 2)
    
    ' Set the date in column E
    rg.Offset(0, 3).Value = DateSerial(yy, mm, dd)
  
Next

End Sub

Insert the code into your VBE module.

VBA Script declares the variables rge, yy, mm, and dd. Assign the range of cells B2:B16 to the variable rge. In each iteration, the script split the Year, Month and Day values from the cell value using LEFT, MID and RIGHT functions. Finally store the date in the respective cell in column E.

To run the ConvertToDate macro. Press Alt + F8, the keyboard shortcut to open the Macro dialog box, Choose the ConvertToDate Macro, and then Press Run.

Convert 8 digits to Date in Excel with Office Script

Office script works in both desktop and web versions. It can be used to record the repetitive task, automate, edit and run. You want to loop through the range of cells, use the Substring function to split the text, and the plus + operator to join the text.

function main(workbook: ExcelScript.Workbook) {

    // Assign the range of cells to the variable
    let ws = workbook.getWorksheet("Office Scripts");
    let rng = ws.getRange("B2:B16");
    let rows = rng.getRowCount();

    // loop through the selected range of cells
    for (let i = 0; i < rows; i++) {

        // get current cell value and store it in the variable cellValue
        let cellValue = rng.getCell(i, 0).getValue() as string

        // use substring function to set year, month and day variable
        let yy = cellValue.substring(0,4)
        let mm = cellValue.substring(4, 6)
        let dd = cellValue.substring(6, 8)
        
        // set the value to column E
        rng.getCell(i, 3).setValue(yy+"-"+mm+"-"+dd)
    }
}

Script assigns the range of cells B2:B16 to the variable rng, and loops through each cell in the given range rng. Each iteration script gets the current cell value and split the Year, Month and Day with the help of a Substring function and stores it in yy, mm, and dd variables. Finally join the variables yy, mm & dd, and set the value to the cell in column E.

Follow the below steps to execute the Convert 8 digits to Date Office script in your excel.

1. Go to Automate menu.
2. Select the Convert 8 digits to Date Script – to open the Code editor on the right side of your Excel application.
3. Press Run 

Conclusions

Sometimes, you received the date is not formatted properly and you cannot use it directly in your report.  You need to process the data before use in your report or automation in Excel. Properly formatted data will help you to quickly generate or debug reports.

You learned Excel Text to Columns to convert text to date, it’s an easy method to convert. Also, Explored in detail how the Excel functions RIGHT, LEFT, MID, DATE, and TEXT are worked to format the date column in Excel.

The Power Pivot and Power Query methods work well for multiple data sources or large data files. You can use either VBA or Offices Scripts code to Convert 8 digits to Date in your dashboard or report automation projects.

Do you know any other method to Convert Text to Date Format in Excel? Let me know in the comments below!