Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Convert Serial Number to Date in Excel (7 Simple Ways)

For one thing, Excel by default stores time and dates in serial numbers which are converted to dates only by changing the formatting of the cells. For example, if we use 1 as a serial number, then the serial number will be converted into a date;1/1/1900. In this article, we’ll explore 7 ways how to convert serial number to date in Excel. Moreover, we’ll also learn to convert 8-digit numbers to date.

Overview of converting serial number to date

The GIF above is an overview of this article, which represents the conversion of serial number to date. In the following sections, we’ll learn more about the dataset and observe each method in detail.


Download Practice Workbook


7 Ways to Convert Serial Number to Date in Excel

First of all, let’s suppose the List of Serial Numbers dataset shown in the B4:C16 cells containing the Serial Number and Expected Format columns respectively. Here, we want to transform the serial numbers into the date format shown below. So, let’s see each method individually and step-by-step.

Dataset for converting Excel serial number to date

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.


1. Converting Serial Number to Date with Format Cells Option

In the first place, let’s start with the simplest and most obvious way to change serial numbers to date, that is to say, we’ll use the Format Cells option.

📌 Steps:

  • Initially, select the C5:C16 cells >> Right-click to open the Contextual Menu >> choose the Format Cells option.

Choosing format cells option from contextual menu

  • Next, in the Format Cells window, select Date >> click the Type (“3/14/2012”) shown below >> hit OK.

Selecting Date format in the format cells window

Ta-dah! That is how simple it is to convert serial number to date in Excel.

Excel serial number to date with format cells option


2. Using VALUE Function to Convert Serial Number to Date

Besides, we can use Excel’s VALUE function to convert serial number to date. Here, the VALUE function converts a string of text to a number.

📌 Steps:

=VALUE(B5)

For instance, the B5 cell refers to the “Serial Number: 44474”.

Excel serial number to date with VALUE function


3. Utilizing TEXT Function to Transform Serial Number to Date

Alternatively, we can also use the TEXT function which converts a value into the specified format.

📌 Steps:

  • First and foremost, enter the C5 cell >> type the following expression >> hit ENTER.

=TEXT(B5,"mm/dd/yyyy")

In this case, the B5 cell points to the “Serial Number: 44474” while, the “mm/dd/yyyy” is the specified date format.

Excel serial number to date with TEXT function


Similar Readings:


4. Turning Serial Number to Date with Keyboard Shortcuts

Now, if you’re wondering if there are any shortcuts to changing serial number to date? Then our next method answers this exact question.

📌 Steps:

  • To begin with, choose the C5:C16 cells >> click the CTRL + 1 keys on your keyboard.

Using keyboard shortcut for format cells dialog box

  • Then, choose the Date tab >> select the Type (“3/14/2012”) shown below >> click on OK.

Selecting Date format in the format cells window

Voila! The results should appear in the picture shown below.

Excel serial number to date with keyboard shortcut


5. Implementing Number Feature to Convert Serial Number to Date

Conversely, another handy feature found in the Home Ribbon is the Number feature which contains a list of the most popular and useful number formats.

📌 Steps:

  • At the very beginning, highlight the C5:C16 cell >> click the drop-down arrow >> select Short Date.

Choosing short date format

  • Likewise, copy the serial numbers to the D5:D16 cells >> click the Long Date option.

Choosing long date format

  • Finally, the results should look like the figure shown below.

Excel serial number to date with number feature


6. Changing Serial Number to Date with Mathematical Operators

In addition, Excel’s mathematical operators can also transform serial numbers to date, so just follow along.

📌 Steps:

  • To start with, enter any of the following formulas to obtain the output as shown in the image below.

=B5+0

=--B7

=B9*1

=B13/1

In this scenario, the B5, B7, B9, and B13 cells represent “Serial Numbers” whereas the (+), (– –), (*) and ( / ) are the Addition, Double Negation, Multiplication, and Division operators.

Excel serial number to date with mathematical operators


7. Applying VBA Code to Convert Serial Number to Date

In addition, if you often need to turn serial numbers to date, then you may consider the VBA Code below. It’s simple & easy, just follow along.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Opening Visual Basic in the developer tab

  • Second, go to the Insert tab >> select Module.

Inserting module

For ease of reference, copy the code from here and paste it into the window as shown below.

Sub serial_to_date()

Dim arr As Range
Dim val As Range
Set arr = Application.Selection

For Each val In arr
val.Offset(0, 1).Value = CDate(val.Value)
Next val

End Sub

VBA code for excel serial number to date

Code Breakdown

Now, in the following section, we’ll explain the VBA code used to convert serial number to date.

  • In the first portion, the sub-routine is given a name, here it is serial_to_date().
  • Next, define the variables arr and val as Range.
  • Then, use the Set statement to store the selected cells into the arr variable.
  • Lastly, use a For Loop to iterate through each value and apply the Offset property to paste the values in the adjacent column.

VBA code explanation

  • Third, select the B5:B16 cells >> click the Macros button >> hit Run.

Executing macro

Subsequently, the results should look like the screenshot given below.

Excel serial number to date with VBA code


How to Convert 8-Digit Number to Date in Excel

Last but not least, we can convert 8-digit numbers to Excel’s date format using the DATE, RIGHT, MID, and LEFT functions. So let’s see it in action.

📌 Steps:

  • At the start, navigate to the C5 cell >> insert the following expression into the Formula Bar >> press the ENTER key >> drag the Fill Handle tool to copy the formula to the cells below.

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

Formula Breakdown
  • RIGHT(B5,4)  returns the specified number of characters from the end of a string. Here, the B5 cell is the text argument whereas 4 is the num_chars argument such that the function returns the 2 characters from the right side.
    • Output → “2016”
  • MID(B5,3,2) returns the characters from the middle of a text string, given the starting position and length. Here, the B5 cell is the text argument, 3 is the start_num argument, and 2 is the num_chars argument such that the function returns the first character from the left side.
    •  Output → “03”
  • LEFT(B5,2) returns the specified number of characters from the start of a string. Here, the B5 cell is the text argument whereas 2 is the num_chars argument such that the function returns the 2 characters from the left side.
    •  Output → “10”
  • DATE(RIGHT(B5,4), MID(B5,3,2), LEFT(B5,2))  becomes
    • DATE(“2016”, “03”, “10”)  returns number that represents date in Microsoft Excel date-time code. Here, the “2016” is the year argument, next  “03” is the month argument, and “10” is the day argument.
    • Output → 42439

📃 Note: You can open the Format Cells dialog box by pressing CTRL + 1 and change the cell formatting to date.

Excel serial number to date with DATE, LEFT, RIGHT, and MID functions


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

To sum up, we hope this article helps you understand how to convert serial number to date in Excel. Now, if you have any queries, please leave a comment below. And, you can read more articles like this on our website ExcelDemy.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo