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.
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.
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.
Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.
1. Converting Serial Number to Date in Excel 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.
- Next, in the Format Cells window, select Date >> click the Type (“3/14/2012”) shown below >> hit OK.
Ta-dah! That is how simple it is to convert serial number to date in Excel.
Read More: How to Convert Number to Date in Excel
2. Using Excel 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:
- First of all, move to the C5 cell >> enter the formula given below >> press ENTER >> change the number formatting to date as shown previously.
=VALUE(B5)
For instance, the B5 cell refers to the “Serial Number: 44474”.
3. Applying TEXT Function to Transform Serial Number to Date in Excel
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.
4. Changing Serial Number to Date with Keyboard Shortcuts in Excel
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.
- Then, choose the Date tab >> select the Type (“3/14/2012”) shown below >> click on OK.
Voila! The results should appear in the picture shown below.
Read More: How to Convert General Format to Date in Excel
5. Using Excel 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.
- Likewise, copy the serial numbers to the D5:D16 cells >> click the Long Date option.
- Finally, the results should look like the figure shown below.
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.
7. Applying Excel 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.
- Second, go to the Insert tab >> select 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
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.
- Third, select the B5:B16 cells >> click the Macros button >> hit Run.
Subsequently, the results should look like the screenshot given below.
Read More: How to Convert Number (YYYYMMDD) to Date Format in Excel
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))
- 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.
Download Practice Workbook
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.
