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.

Overview to Convert Number to Date


How to Convert Number to Date in Excel: 6 Easy Ways

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.

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.

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


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.

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.


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.

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.

Read More: How to Convert Serial Number to Date 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, 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.

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


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.

Using VBA to Convert Number to Date

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.

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.

Read More: How to Convert Text to Date in Excel


Download Practice Workbook

Download the following workbook and exercise.


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo