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 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 2: Format Cells window pops up. In the Format Cells Command Box, Select Date (in Number section below the Category options).
Step 3: Click OK. In a moment, all the selected serial numbers convert to dates.
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
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.
Step 2: Choose a cell in the range (i.e., C4). Type the following formula in the cell.
In the formula, B4; is the text reference of the serial number.
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.
Method 3: TEXT Function to Convert Serial Number to Date
The syntax of the TEXT function is
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).
Inside the formula,
B4; is the cell reference.
“mm/dd/yyyy”; declares the format as Date.
Step 2: Hit ENTER and then Drag the Fill Handle, the consequences will be similar to the picture below.
We can use different format_text such as dd/mm/yy, yyyy/mm/dd etc.
- How to Convert Number to Date in Excel (6 Easy Ways)
- Convert General Format to Date in Excel (7 Methods)
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.
Step 2: The outcomes will be like the image below.
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).
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.
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)
Step 1: Select any blank cell (i.e., C4). Paste the following formula.
Step 2: Hit ENTER and the serial number converts to date.
Now repeat Steps 1 and 2 with different mathematical operators. You can see the results depicted in the image below.
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.
Step 2: Press ALT+F11 altogether. The Microsoft Visual Basic window will open up.
Step 3: From the Toolbar, Select Insert > 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
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.
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.