# How to Convert Number to Date in Excel

Get FREE Advanced Excel Exercises with Solutions!

Sometimes in Excel, we input the date but it returns it as a bunch of numbers as it stores the date value as a number. This makes the dataset very difficult to understand. In this article, we are going to learn 6 easy ways to convert a number to a date in Excel.

Here, I have tried to show the overview where I have used Number Formatting to convert a date to Excel. Go through the below section for more methods with simple explanation. ## 6 Easy Ways to Convert Number to Date in Excel

### 1. Use Number Format Drop-Down to Convert Number to Date

Using the Number format drop-down from the ribbon, we can quickly convert numbers to date. Assuming we have a dataset (B4:D10) of the customers’ payment methods with dates. We can see that the dates are displayed as numbers. Now we are going to convert these numbers into date format. STEPS:

• First, select the cell range D5:D10.
• Next, go to the Home tab.
• Then select the drop-down from the Number section.
• After that, select ‘Short Date’ or ‘Long Date’ from the drop-down. • Finally, we can see that all the numbers are converted into dates. ### 2. Apply Built-In Date Format Option for Converting Number to Date

Excel has some built-in format options to convert numbers to date. Suppose, we have a dataset (B4:D10) of different customers’ pay amounts with the date. In range D5:D10, we are going to convert numbers to date. STEPS:

• Select the cell range D5:D10 at first.
• Then go to the Home tab.
• Now from the Number section of the ribbon, press the Dialog Launcher icon at the right-bottom corner. • Here we can see that a Format Cells window pops up.
• After that, go to the Number tab.
• Next from the ‘Category’ box, select ‘Date’.
• From the ‘Type’ box, select which format we want to see as a date.
• Click on the OK. • In the end, we can see the result. ### 3. Create Custom Date Formatting to Convert Number to Date in Excel

We can create customized date formatting in Excel. This helps us in making the dataset friendly. From the dataset (B4:D10), we are going to apply the customized date format in cell range D5:D10. STEPS:

• First, select the range D5:D10.
• Then go to the Home tab > Number section > Dialog Launcher icon.
• A Format Cells window opens up.
• Now go to the Home tab.
• Here, from the ‘Category’ box, select ‘Custom’.
• Next, in the ‘Type’ box, write down the desired format. We type “dd-mm-yyyy” there.
• Finally, click on OK. • We can see the result finally. ### 4. Use Text Function to Convert Number to Date

To return the number as text with a given format, we can use the TEXT function. We can use this function for converting numbers into dates.  A payment dataset (B4:D10) is here. We are going to convert the numbers in cell range C5:C10 into a date in cell range D5:D10. STEPS:

• Select Cell D5 at the beginning.
• Next type the formula:
`=TEXT(C5,"dd-mm-yyyy")` • Lastly, hit Enter and use the Fill Handle tool to autofill the cells. ### 5. Combine DATE, RIGHT, MID, LEFT Functions to Convert 8 Digits Number to Date

Excel along with the combination of RIGHT, MID, LEF functions help us to convert numbers containing 8 digits into dates. All the values we want to convert must be in the same pattern. The DATE function helps us to calculate the Excel date. As well as the RIGHT function extracts the characters from the right side of the text string. To extract the characters from the middle of the text string, we can use the MID function. Moreover, the LEFT function helps us to extract the characters from the left side of the text string.

Let’s say we have a dataset (B4:D10). The cell range C5:C10 contains 8 digits or characters in each. STEPS:

• Select Cell D5.
• Now type the formula:
`=DATE(RIGHT(C5,4),MID(C5,3,2),LEFT(C5,2))` • Then press Enter and use the Fill Handle tool to see the result. Formula breakdown

RIGHT(C5,4)

This will extract the last four digits of the text string and return them as year value.

MID(C5,3,2)

This will extract the middle two digits of the text string and return as month value.

LEFT(C5,2)

This will extract the first two digits of the text string and return as day value.

DATE(RIGHT(C5,4),MID(C5,3,2),LEFT(C5,2))

This will return the full date in “dd-mm-yy” format.

### 6. Use VBA to Convert Number to Date in Excel

Microsoft Visual Basic for Application can help us to convert numbers to date very quickly. We have a dataset (B4:D10) of payment amounts with date numbers. STEPS:

• First, select all the numbers in a range that we want to convert.
• Next, select the sheet from the sheet tab.
• Right-click on it and select View Code. • A VBA module opens up.
• Now type the 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``````

• Then click on the Run option. • Finally, we can see the result in the cell range D5:D10. ## Conclusion

These are the quickest ways to convert numbers to date in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

## Related Articles #### Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

1. Reply Here is a simpler method to convert an 8-digit number to date (dd/mm/yyyy)

In cell A1 we have this number: 16062020

Use this formula:

=TEXT(A1,”00\/00\/0000″)+0

• Reply Hello Meni Porat,
Your formula may show a #VALUE error for an 8-digit number. However, removing +0 from the formula will return the correct result. If you have any other suggestions or face any problems, please share them with us in the comment section.
Regards
Arin Islam,
Exceldemy. Advanced Excel Exercises with Solutions PDF  