How to Convert Serial Numbers to Dates in Excel?- Easy Ways

Date and time values are usually stored as serial numbers in Excel. For example, 11 June 2022 in a cell is stored as a number in Excel. The benefits of this format are that it enables users to easily subtract dates/times and add them in Microsoft Excel. You may sometimes expect to see a date in a cell; instead, you see a number. You can convert these serial numbers into dates by following these easy steps.

Table of Contents

Using the Number Formatting Method

It is usually considered to be the easiest method. It involves changing the formatting of the cells in the worksheet with the numbers. There are two ways;

Using an In-Built Date Format Option

Follow these steps;

1. Select those cells which have numbers that you want to convert to date

2. Go to the Home Tab

3. Select the Number Group and click on the Number Formatting option

4. Choose the Long Date or Short Date choice depending on the kind of format you want the numbers to be converted into a date

5. Press OK

These steps, however, do not change the value of the cell. Excel typically picks up the short date formats based on the system's default setting. The method is limited because it only has two date formats, the short date and the long date. It cannot also display the date and time using the formatting options in the choices given in the box. For flexibility purposes, use the following method.

Using the Number Formatting Dialog Box

Follow the simple steps below.

1. Select cells that contain numbers you want to convert into dates

2. Go to the Home Tab

3. Select the Numbers group and click on the launcher icon

4. Choose the Format Cells option and select the Number tab

5. Go to Category, then click on Date

6. Choose the format you desire from the Type box

7. Enter OK

The numbers selected are finally converted to dates. You can even create the format you desire if you don't find the one you are looking for. In this case, follow these steps.

Select the cells containing numbers you want to change to date
Go to Home Tab
Choose the Numbers Group and click on the launcher icon

Move to the Format Cells option and select the Number tab
Go to the Category option and click on Custom

Enter mm/dd /yy
Click OK

Using the TEXT Formula Method

This formula is convenient since it shows both the date and the time. For example, the final result can be 01/01/2021 12:00 AM. Hence the formula =TEXT (A2," mm/dd/yyyy hh:mm AM/PM") can be used. It is important to note that you can combine the TEXT formula result with other similar formulas. To convert the texts to numbers in Excel so that this method could work, follow these steps

1. Put an apostrophe before a number