How to Convert Number to Date in Excel

When working with Excel, you might encounter situations where dates are stored as numeric values, making it challenging to interpret the dataset. In this article, we’ll explore six straightforward ways to convert these numeric values back into meaningful dates.

Overview

In the following sections, I’ll explain various methods for converting numbers to dates using Excel. Whether you’re dealing with raw data or need to clean up existing entries, these techniques will help you transform numeric values into more user-friendly date formats.

Method 1 – Use Number Format Drop-Down to Convert Numbers to Dates

Let’s walk through the steps to convert those numeric dates into a more readable date format in Excel. Here’s how you can do it:

1. Select the Cell Range:
   • First, select the cell range containing the numeric dates (in your case, D5:D10).
2. Go to the Home Tab:
   • Next, navigate to the Home tab in the Excel ribbon.
3. Access the Number Format Drop-Down:
   • Look for the Number section in the ribbon.
   • Click on the drop-down arrow next to the number format options.
4. Choose Date Format:
   • From the drop-down menu, select either Short Date or Long Date based on your preference.
   • The Short Date format will display dates like MM/DD/YYYY, while the Long Date format will show the full month name (e.g., January 1, 2024).
     
5. Observe the Result:
   • Once you’ve made the selection, Excel will automatically convert the numeric values in the selected cell range into the chosen date format.

Read More: How to Convert General Format to Date in Excel

Method 2 – Apply Built-In Date Format Option for Converting Numbers to Dates

Let’s walk through the steps to convert those numeric dates into a more readable date format in Excel. Here’s how you can do it:

1. Select the Cell Range:
   • First, select the cell range containing the numeric dates (in your case, D5:D10).
     
2. Go to the Home Tab:
   • Next, navigate to the Home tab in the Excel ribbon.
3. Access the Number Format Drop-Down:
   • Look for the Number section in the ribbon.
   • Click on the drop-down arrow next to the number format options.
     
4. Choose Date Format:
   • From the drop-down menu, select either Short Date or Long Date based on your preference.
   • The Short Date format will display dates like MM/DD/YYYY, while the Long Date format will show the full month name (e.g., January 1, 2024).
5. Observe the Result:
   • Once you’ve made the selection, Excel will automatically convert the numeric values in the selected cell range into the chosen date format.
     
   • And there you have it! Your numeric dates should now be displayed in a more human-readable format.

Method 3 – Create Custom Date Formatting to Convert Numbers to Dates in Excel

We can create customized date formatting in Excel to make our dataset more user-friendly. Here are the steps to apply a customized date format to the cell range D5:D10 from the dataset (B4:D10):

1. Select the Range:
   • First, select the cell range D5:D10.
2. Open the Format Cells Dialog:
   • Go to the Home
   • In the Number section, click on the Dialog Launcher icon (the small arrow in the bottom-right corner).
3. Format Cells Window:
   • The Format Cells window will open.
   • Make sure you are on the Home
4. Choose Custom Format:
   • In the Category box, select
5. Specify the Desired Format:
   • In the Type box, enter the desired date format. For example, type dd-mm-yyyy to display dates in day-month-year format.
6. Apply and Confirm:
   • Click on the OK
   • The customized date format will be applied to the selected cell range (D5:D10).
     
7. View the Result:
   • You should now see the dates in the specified format.

Method 4 – Use the TEXT Function to Convert Numbers to Dates

To display a number as text with a specific format, you can utilize the TEXT function. This function is particularly useful for converting numeric values into date formats. Let’s say we have a payment dataset in cells B4:D10, and we want to convert the numbers in the range C5:C10 into dates in the range D5:D10. Follow these steps:

1. Select Cell D5to begin.
2. Type the following formula:
   
   =TEXT(C5,"dd-mm-yyyy")
   
3. Press Enter.
   
4. Use the Fill Handle tool to autofill the formula down to the other cells in the range D5:D10.

Read More: How to Convert Serial Number to Date in Excel

Method 5 – Using DATE, RIGHT, MID, and LEFT Functions to Convert 8-Digit Numbers to Dates

When dealing with 8-digit numbers, you can employ a combination of Excel functions to convert them into dates. The values you want to convert must follow a consistent pattern. Let’s break down the process:

1. Excel DATE function:
   • The DATE function helps calculate Excel dates.
2. RIGHT Function:
   • Extracts characters from the right side of a text string.
3. MID Function:
   • Extracts characters from the middle of a text string.
4. LEFT Function:
   • Extracts characters from the left side of a text string.

Suppose we have a dataset in cells B4:D10, and the cell range C5:C10 contains 8-digit values. We want to convert these numbers into dates in the range D5:D10. Follow these steps:

1. Select Cell D5.
2. Enter the following formula:
   
   =DATE(RIGHT(C5,4),MID(C5,3,2),LEFT(C5,2))
   
   • Explanation:
     • RIGHT(C5, 4): Extracts the last four digits as the year value.
     • MID(C5, 3, 2): Extracts the middle two digits as the month value.
     • LEFT(C5, 2): Extracts the first two digits as the day value.
     • The entire formula returns the full date in “dd-mm-yy” format.
       
3. Press Enter.
4. Use the Fill Handletool to autofill the formula down to other cells in the range D5:D10.
   

This approach will convert the 8-digit numbers in column C to corresponding dates in column D. Remember to adjust the date format in the formula according to your preference.

Read More: How to Convert 8 Digit Number to Date in Excel

Method 6 – Using VBA to Convert Numbers to Dates in Excel

Microsoft Visual Basic for Applications (VBA) can help us quickly convert numbers to dates. Let’s assume we have a dataset in cells B4:D10 containing payment amounts represented as date numbers.

Follow these steps:

1. Select the Numbers to Convert:
   • First, select all the numbers in the range that you want to convert.
2. Access the VBA Module:
   • Next, navigate to the sheet where your data is located.
   • Right-click on the sheet tab and choose View Code.
   • This will open the VBA module.
     
3. Write the VBA Code:
   • In the VBA module, type the following code:
     

     Sub VBAconvertNumberToDate()
     Dim x As Range
     Dim i As Range
     Set x = Application.Selection
     For Each i In x
     i.Offset(0, 1).Value = CDate(i.Value)
     Next i
     End Sub

4. Run the Code:
   • Click on the Run option (usually a green arrow icon) in the VBA editor.
     
5. View the Results:
   • Finally, check the cell range D5:D10 to see the converted dates.

Read More: How to Convert Text to Date in Excel

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Convert Number (YYYYMMDD) to Date Format in Excel
• How to Convert Text to Date and Time in Excel
• How to Convert Text Date and Time to Date Format in Excel
• Disable Auto Convert to Date in Excel
• Text Won’t Convert to Date in Excel

<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel