5 Ways To Convert Date Number To Date In Excel

In Excel, dates can be stored as numbers, known as serial numbers, which can be confusing when trying to analyze or present data. Fortunately, there are several ways to convert date numbers to dates in Excel. In this article, we will explore five methods to achieve this conversion.

The importance of converting date numbers to dates cannot be overstated. Dates are essential in various industries, such as finance, accounting, and project management, where tracking deadlines, schedules, and timelines is crucial. By converting date numbers to dates, you can better manage and analyze your data, making it more meaningful and useful.

Moreover, converting date numbers to dates can help you avoid errors and inconsistencies in your data. When working with date numbers, it's easy to misinterpret or misformat the data, leading to incorrect calculations and conclusions. By converting date numbers to dates, you can ensure that your data is accurate and reliable.

Method 1: Using the TEXT Function

One way to convert date numbers to dates is by using the TEXT function in Excel. The TEXT function converts a number to text in a specified format. To use the TEXT function, follow these steps:

Select the cell that contains the date number you want to convert.
Go to the Formula Bar and type =TEXT(A1,"mm/dd/yyyy"), where A1 is the cell containing the date number.
Press Enter to apply the formula.

The TEXT function will convert the date number to a date in the format you specified. You can adjust the format to suit your needs.

Method 2: Using the DATE Function

Another way to convert date numbers to dates is by using the DATE function in Excel. The DATE function returns the date corresponding to a given serial number. To use the DATE function, follow these steps:

Select the cell that contains the date number you want to convert.
Go to the Formula Bar and type =DATE(2022,1,1)+A1-1, where A1 is the cell containing the date number.
Press Enter to apply the formula.

The DATE function will convert the date number to a date. Note that this method assumes that the date number is in the format of days since January 1, 1900.

Method 3: Using the Format Cells Option

You can also convert date numbers to dates by using the Format Cells option in Excel. To do this, follow these steps:

Select the cell that contains the date number you want to convert.
Right-click on the cell and select Format Cells.
In the Format Cells dialog box, select the Date category.
Choose the desired date format from the list.
Click OK to apply the format.

The Format Cells option will convert the date number to a date in the format you selected.

Method 4: Using Power Query

If you have a large dataset, you can use Power Query to convert date numbers to dates. To do this, follow these steps:

Select the cell that contains the date number you want to convert.
Go to the Data tab and select From Table/Range.
In the Power Query Editor, select the column that contains the date number.
Go to the Add Column tab and select Date.
In the Date dialog box, select the desired date format.
Click OK to apply the format.

Power Query will convert the date number to a date in the format you selected.

Method 5: Using VBA Macro

Finally, you can use a VBA macro to convert date numbers to dates. To do this, follow these steps:

Open the Visual Basic Editor by pressing Alt+F11 or by navigating to Developer > Visual Basic.
In the Visual Basic Editor, insert a new module by clicking Insert > Module.
Paste the following code into the module:

Sub ConvertDateNumberToDate()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = DateAdd("d", cell.Value - 2, "1/1/1900")
    Next cell
End Sub

Save the module and close the Visual Basic Editor.
Select the cell that contains the date number you want to convert.
Press Alt+F8 to open the Macro dialog box.
Select the ConvertDateNumberToDate macro and click Run.

The VBA macro will convert the date number to a date.

Excel Date Conversion Image Gallery

We hope this article has helped you learn how to convert date numbers to dates in Excel. Whether you use the TEXT function, the DATE function, the Format Cells option, Power Query, or a VBA macro, you can easily convert date numbers to dates and make your data more meaningful and useful.