How to Convert Number to Date in Excel (6 Easy Ways)

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.


Practice Workbook

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.

Using Number Format Drop-Down to Convert Number to Date

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. 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.

Built-In Date Format Option for Converting Number 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.

Built-In Date Format Option for Converting Number to Date

  • 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.

Create Custom Date Formatting to Convert Number to Date

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.


Similar Readings:


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.

Using Text Function to Convert Number to Date

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.

Formula breakdown

C5

This will be the numeric value of the number range.

“dd-mm-yyyy”

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.

Combining DATE, RIGHT, MID, LEFT Functions to Convert 8 Digits Number to Date

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. 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.

Using VBA to Convert Number to Date

STEPS:

  • 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.

Using VBA to Convert Number to Date

  • 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 Readings

Nuraida Kashmin

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo