Nuraida Kashmin
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.
Download Practice Workbook
Download the following workbook and exercise.
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.
Read More: How to Convert General Format to Date in Excel (7 Methods)
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.
Read More: How to Convert Serial Number to Date in Excel (7 Simple Ways)
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.
Read More: Stop Excel from Converting Date to Number in Formula (2 Ways)
Similar Readings
- How to Convert Active Directory Timestamp to Date in Excel (4 Methods)
- Disable Auto Convert to Date in Excel (2 Methods)
- How to Convert SAP Timestamp to Date in Excel (4 Ways)
- Convert Timestamp to Date in Excel (2 Suitable Cases)
- How to Convert Week Number to Date in Excel (2 Suitable Methods)
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.
Read More: How to Convert Number (YYYYMMDD) to Date Format in Excel
5. Combine DATE, RIGHT, MID, LEFT Functions to Convert 8 Digits Number to Date
Excel DATE function 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.
Read More: How to Convert 13 Digit Timestamp to Date Time in Excel (3 Ways)
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.
Read More: Excel VBA to Convert Date and Time to Date Only
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
- How to Convert Text Filter to Date Filter in Excel (3 Examples)
- Text Won’t Convert to Date in Excel (4 Problems & Solutions)
- How to Extract Month and Day from Date in Excel (6 Methods)
- Convert Date to Quarter and Year in Excel
- How to Convert Date to Julian Date in Excel (3 Easy Ways)
- Convert Unix Timestamp to Date in Excel (3 Methods)
Here is a simpler method to convert an 8-digit number to date (dd/mm/yyyy)
In cell A1 we have this number: 16062020
Instead of: =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))
Use this formula:
=TEXT(A1,”00\/00\/0000″)+0
Hello Meni Porat,
Thanks for your kind suggestions.
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.