How to Convert Serial Numbers to Date in Excel

When importing dates from external sources into Excel, the data often transfers over in a number coded form, such as 42067.

Excel, which has over 484 functions, has its own way of storing data.

It stores dates as numbers and then adjusts them in accordance with the date requirement set by the user.

For this reason, you will want to know how to Convert Serial Numbers to Date in Excel.

Although this method of storing data appears counterintuitive, it however is thoughtful because excel can conduct more mathematical operations with dates in numerical form.

However, because this date format is somehow hard for humans to comprehend, it is required to convert these number codes (serial numbers) to a familiar date format that can be read by both the user and a third party.

The purpose of this tutorial is therefore to show you different ways by which you can successfully Convert a Serial Number to Date Format in Excel.

Before I show you ways to go about this, I will shed more light on the concept of serial numbers.

Contents:

The Idea of Serial Numbers

Dates are stored in excel as serial numbers or integers.

The series began from January 1, 1900, and every other day since then has always had an addition of one in the serial number form.

That means the serial number 1 appeared on January 1, 1900, the serial number 2 appears the day after, the serial number 3 appear the third day, the serial number 4 appears the fourth day, etc.

So, the count started from January 1, 1900, and follows through each day until now.

Because June 10, 2019, exactly is 43,627 days after January 1, 1900, the serial number is 43627.

January 1, 1900 is a date default setting in all versions of excel and as such is very instrumental in understanding the date convention on excel.

So, any date you must work with in excel has to be with reference to January 1, 1900.

Now that the coordinate of the subject matter has been set, let’s go on to Converting Serial Numbers to Date in Excel.

Converting Serial Numbers to Date in Excel

In this section, I will be showing you step by step procedures on converting serial numbers to date in excel.

There are three methods I will be discussing under this section, so please pay attention to every detail.

The set of serial numbers we will be working with is shown below:

We will utilize three different methods in converting the serial numbers above to dates in different formats.

Method 1 – Using the TEXT Method to Convert a Serial Number to a Date

In excel, TEXT function turns any numeric data (such as date, time, or currency) by changing it to text in the specified format.

This function can be used to convert serial numbers to date in excel.

Let’s now see the TEXT function syntax:

= TEXT(serial_number, date_format_code)

Next, let me explain to you the terms of the syntax,

The integer value reference to the cell you wish to translate to date is serial number
The date format to which you wish to translate the serial number is specified by date format code

The TEXT method basically applies the date format code that you chose to the serial number provided and displays a text format in response to your input.

For example, if cell A3 has the serial number “43151”=TEXT(A3,”dd/mm/yy”) returns “20/02/18”.

Days of the Month Format Codes:

The format codes below can be used to express values representing day:

d- This is used for one or two-digit symbolization of the day (e.g.: 2 or 20)
dd- This is used for two-digit symbolization of the day (e.g.: 02 or 20)
ddd- This is used to represent weekdays in an abbreviated form (e.g.: Wed, Thur)
dddd- This is used to represent the full name of weekdays (e.g.: Wednesday, Friday)

So,

The sample dataset will return “20” if you enter =TEXT(A3, “d”).
The sample dataset will return “20” if you enter =TEXT(A3, “dd”).
The sample dataset will return “Sat” if you enter =TEXT(A3, “ddd”).
The sample dataset will return “Saturday” if you enter =TEXT(A3, “dddd”).

Year Format Codes:

The format codes below can be used to express values representing years:

yy- This is used for a two-digit symbolization of the year (e.g.: 20 or 22)
yyyy- This is used for a four-digit symbolization of the year (e.g.: 2020 or 2022)

So,

Entering =TEXT(A2, “yy”) to the data we are working with displays “20”
Entering =TEXT(A2, “yyyy”) to the data we are working with displays “2020”

Month Format Codes:

The following basic format codes can be used to express month values:

m- This is used for a one- or two-digit symbolization of the month (e.g.: 7 or 11)
mm- This is used for a two-digit symbolization of the month (e.g.: 07 or 11)
mmm- This is used to represent an abbreviated version of a month’s name (e.g.: Jan or Feb)
mmmm- This is used to represent a month’s name in full (e.g.: January or February)

And,

Entering =TEXT(A2, “m”) with the data given displays “3”.
Entering =TEXT(A2, “mm”) with the data given displays “03”.
Entering =TEXT(A2, “mmm”) with the data given displays “Mar”.
Entering =TEXT(A2, “mmmm”) with the data given displays “March”.

Now that we have gotten the procedures on how to use the TEXT function, let’s now see how we can apply it pictorially.

We will start by converting our number codes in column B to the format shown in column C as illustrated in the example below:

Outlined below are methodical patterns to getting the results above:

Select a blank cell in which you wish the changed date format to appear (C2)
Input the command: =TEXT(A2,”mm/dd/yy).
Press enter.
Doing this should give us the serial number in the date format we desire. By pulling down the fill handle or double-tapping on it, you may copy this to the remainder of the cells in the column.
By hitting CTRL+C (if you are on a Mac), you can copy the formula results from this column.

Place your mouse on the column and click its right button and select paste values from the options menu that shows.

The command results will be saved as values in the same column. If you like, you may now delete column B.

Depending on the format code selected in procedure 2, the output in column C will be different.

Here are several format codes and the types of results possible to get if you apply them to cell B2:

This translates to the fact that you can convert serial numbers to a date format you like using the TEXT method.

All you have put in place is to alter the format code to suit your needs.

Note that your translated date will be in form of text using this technique.

You can also use the format cells function to convert it to date format.

Method 2 – Using Format Cells to Convert a Serial Number to a Date

The Format Cells dialog box in excel is used in this manner.

The dialog box of the format cell is quite versatile, allowing you to conduct a variety of formatting tasks from a single location.

The number codes (serial number) in the data we are working with, can be converted to dates using format cells as follows:

Choose the cells that contain the serial numbers you wish to translate to a readable form (B2:B6).
Click on format-cells from the menu that comes when you right-click on your selection. You may also choose the dialogue box launcher from the mini number tab on the home tab.

The format cells dialogue box will appear. Select the number tab.
Select the date option from the category drop-down menu.
On the right side, you will see a number of format possibilities for the date.

Choose your preferred format.
Use the option from the category list if you don’t see a choice for the format you wish to use. This allows you to change the date format of the cell.
Check the format codes under Type to see if there is an availability of your desired code format. If you don’t have a format code, type it in the input box directly below
Then hit the ok button to close the dialog box of the format-cell.

All the selected cells should now be converted to dates in the format you want.

You may rename the column from Serial Numbers to Date.

Method 3 – Using VBA to Convert a Serial Number to a Date

If you know how to code or use VBA, you may use this way to swiftly convert all of your serial numbers to dates in one go.

Below is the VBA code, you can save it for use later.

Sub convert_serial_num_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

If you wish to apply the aforementioned code to your dataset, here are the steps to take:

Select Visual Basic from the developer menu ribbon
Click insert-> Module once your VBA window has opened. You can now begin coding. In the module window, type or copy-paste the above lines of code. Your program is now ready to execute.
Select the range of cells in your worksheet that contain the serial numbers you want to convert. The codes will display the result in the column next to it, so make sure it is blank.
Proceed through Developer->Macros->convert_serial_num_to_date->Run