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 some ways to convert a number to a date in Excel.
Download the following workbook and exercise.
6 Easy Ways to Convert Number to Date in Excel
1. Using 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.
- 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. 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.
- 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.
- 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.
- How to Convert Text to Date in Excel (10 ways)
- Convert General Format to Date in Excel (7 Methods)
- Convert Text to Date and Time in Excel (5 Methods)
4. Using 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.
- Select Cell D5 at the beginning.
- Next type the formula:
- Lastly, hit Enter and use the Fill Handle tool to autofill the cells.
➥ Formula breakdown
This will be the numeric value of the number range.
This will be the date format that we want to convert from the number. We can use “mm-dd-yy”, “mm/dd/yy”, “dddd, mmmm d,yyyy”, and many more date formats in the formula text option of the TEXT Function.
5. Combining DATE, RIGHT, MID, LEFT Functions to Convert 8 Digits Number to Date
Excel DATE function along with the combination of RIGHT, MID, LEFT 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.
- Select Cell D5.
- Now type the formula:
- Then press Enter and use the Fill Handle tool to see the result.
➥ Formula breakdown
This will extract the last four digits of the text string and return them as year value.
This will extract the middle two digits of the text string and return as month value.
This will extract the first two digits of the text string and return as day value.
This will return the full date in “dd-mm-yy” format.
6. Using 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.
- First, select all the numbers in a range which 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.
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.