Convert Serial Number to Date in Excel (7 Easy Ways)

Excel by default stores time and dates in serial numbers. The serial numbers get 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 use VALUE and TEXT functions, various Excel features as well as VBA Macro Code to convert serial numbers to dates.

Suppose, in a dataset, we have serial numbers similar to the image below.

Dataset-Convert Serial Number to Date in Excel

Dataset for Download

7 Easy Ways to Convert Serial Number to Date in Excel

Method 1: Format Cells to Convert Serial Number to Date

One of the easiest ways to convert a serial number to date is by changing the cell’s format to date. We can change the cell’s format by Excel’s Format Cells feature. In this case, we copy the Serial Numbers in the Equivalent Date column to show the contrast of the method.

Step 1: Select the range (i.e., C4:C17) in the Equivalent Date column. Then hover to Home Tab > Select Format (in Cells section) > Click on Format Cells (from options).

format cells-Convert Serial Number to Date in Excel

Step 2: Format Cells window pops up. In the Format Cells Command Box, Select Date (in Number section below the Category options).

Format cell category

Step 3: Click OK. In a moment, all the selected serial numbers convert to dates.

Format cells final result-Convert Serial Number to Date in Excel

In the Equivalent Date column, the Date format is “mm/dd/yyyy” for every cell in the range. You can choose any types of Date Formats you desire from the Type drop-down list or you can use a Custom one.

Method 2: VALUE Function to Convert Serial Number to Date

The VALUE function is quite handy to convert any text string to value. The syntax of the VALUE function is

=VALUE (text)

As the Serial Numbers are in text format, we can transform them into values. If the transformed values are put into pre-formatted cells as Date, the serial numbers appear as dates.

Step 1: Select the Range where you want to put the serial numbers as dates. Then Follow Steps 1 and 2 from Method 1 to bring up the Format Cells Dialog Box to pre-format the range. Then Select Date Type (i.e., mm/dd/yyyy) and Click OK. All the cells within the range get pre-formatted.

value function-Convert Serial Number to Date in Excel

Step 2: Choose a cell in the range (i.e., C4). Type the following formula in the cell.

=VALUE(B4)

In the formula, B4; is the text reference of the serial number.

value formula

Step 3: Press ENTER and Drag the Fill Handle. Afterward, the rest of the cells convert into values. As we previously format the cells as Date, the outcome values result in Date format similar to the picture below.

Value Function final result-Convert Serial Number to Date in Excel

Method 3: TEXT Function to Convert Serial Number to Date

The syntax of the TEXT function is

= TEXT (value, format_text)

The Text function offers two arguments, one is the value as a reference and the other one is format_text as what format we want to display the value.

Step 1: Insert the following formula in any cell (i.e., C4).

=TEXT(B4,”mm/dd/yyyy”)

Inside the formula,

B4; is the cell reference.

“mm/dd/yyyy”; declares the format as Date.

Text function-Convert Serial Number to Date in Excel

Step 2: Hit ENTER and then Drag the Fill Handle, the consequences will be similar to the picture below.

Text function result-Convert Serial Number to Date in Excel

We can use different format_text such as dd/mm/yy, yyyy/mm/dd etc.


Similar Readings:


Method 4: Keyboard Shortcuts to Convert Serial Number to Date

In Method 1, we discussed the Format Cells feature. We can bring up the Format Cells dialog box via Keyboard Shortcuts.

Step 1: Press CTRL+1, the Format Cells dialog box appears. Choose the desired Date Type then Click OK.

keyboard shortcuts-Convert Serial Number to Date in Excel

 Step 2: The outcomes will be like the image below.

keyboard shortcuts result

Method 5: Number Feature to Convert Serial Number to Date

Excel has a Number section in the Home tab. We can use the Number feature to declare the types of any text or value. As we have serial numbers as text, we can define them as various types of date formats via the Number feature. For the sake of comparability, we copy the serial numbers in columns named Short Date and Long Date.

Step 1: Select the whole range of column Short Date. Then go to Home Tab>  Select Short Date command (from Number section).

number feature-Convert Serial Number to Date in Excel

The whole range of serial numbers converts into Short Dates. Repeating Step 1 for Long Date brings up the result similar to the following picture.

number feature final result

Method 6: Mathematical Operators to Convert Serial Number to Date

Excel functions and features convert serial numbers to dates. Mathematical Operators such as Addition (+), Double Negation (–), Multiplication (*) and Division ( / ) simply change a text string into a date.

Step 1: Paste any of the following Formulas in random cells within the range (i.e., C4:C17)

Addition: =B4+0

Double Negation: =–B6

Multiplication: =B9*1
Division: =B14/1

Step 1: Select any blank cell (i.e., C4). Paste the following formula.

=B4+0

mathematical operator-Convert Serial Number to Date in Excel

Step 2: Hit ENTER and the serial number converts to date.

mathematical operator result

Now repeat Steps 1 and 2 with different mathematical operators. You can see the results depicted in the image below.

mathematical operator with formula

 The only condition is not to change the date values and Mathematical Operators obliges it.

Method 7: VBA Macro Code to Convert Serial Number to Date

VBA Macro Code converts serial numbers very swiftly. If you have a basic understanding of VBA macro code you can just modify the delivered code or use the same exact code nonetheless. Before running the macro code, the entries look something like the following picture.

Step 1: First select the cell you want to convert in the worksheet.

vba macro code-Convert Serial Number to Date in Excel

Step 2: Press ALT+F11 altogether. The Microsoft Visual Basic window will open up.

Step 3: From the Toolbar, Select Insert > Module.

vba macro code module

 Step 4: In the Module, paste following the VBA Macro Code.

Sub convert_serial_number_to_date()
Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = CDate(cell.Value)
Next cell
End Sub vba macro code 

 In the macro code, the Range(rng) is set to the selection of any range or cell. And cell offset keeps selection default. In the end, it converts all the cells of selection.

Step 5: Press F5 to run the macro code then back to the worksheet. You’ll see VBA Macro Code converts all the selected cells to dates.

vba macro code result

Conclusion

To convert serial numbers to dates we use TEXT and VALUE functions, various Excel features, mathematical operators, and VBA Macro Code. Each of the methods has its own convenience. You can choose the methods that best suit your purpose. Hope you find the above-discussed steps are lucid and handy to work with. Comment, if you need further clarifications and have something to add.


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