2 Useful Methods to Convert Numbers to Dates in Your Excel Worksheet

The numbers in cells will not turn into dates automatically in a certain condition. Therefore, in this article, we will provide two methods to help you solve this problem.

You will certainly need to input dates into excel worksheet. And there are many different types of date format. On the other hand, you will also copy some dates into Excel cells. However, if there is no symbol for dates, i.e. the hyphen symbol (-) or the slash symbol (/), those numbers will not change into dates automatically. This image below shows such an example.

Apparently those numbers are dates and the format is YMD. However, they will not change into dated without certain symbols. If you change them one by one, you will certainly spend a lot of time and energy. At this moment, you can use the two methods in the coming parts.

Method 1: Use Excel Functions

In this method, you will need to use the LEFT and RIGHT functions in formula.

1. Click a blank cell in the worksheet. Here we will choose the cell B2 in this worksheet.
2. And then input this formula into the blank cell:

=LEFT(RIGHT(A2,4),2)&”/”&RIGHT(A2,2)&”/”&LEFT(A2,4)

The LEFT function and the RIGHT function will extract the year, month and the day in the original cell. Here we will use this date format Month/Day/Year. If you need to use other format, you can also change the order of each part in this formula.

3. After that, press the button “Enter” on the keyboard. And you will get the result in the cell B2.
4. In this step, click the cell B2 again.
5. And then double click the fill handle of this cell. Thus, all the numbers will be in the form of date.
6. However, you need to pay attention that the results are produced by formulas. You need to have additional settings. Select the target cell in column B.
7. And then press the shortcut keys “Ctrl +C” on the keyboard.
8. Next right click another blank cell. Here we will click the cell C2.
9. In the new menu, choose the option “Values” for the paste options. Thus, all the result will be in the new column.
10. After that, double click the cell C2.
11. Next press the shortcut keys “Enter” on the keyboard. Therefore, the cell format of the cell C2 will be “Date”.
12. In this step, click the C2 again.
13. And then double click the fill handle of the cell C2.
14. Now you have finished all the settings. You can also delete the original numbers and the formulas.

Method2: Use the Text to Columns Feature

There are still many steps in the above part. If you need to finish this task quicker, you can consider using the second method. In this part, we will introduce how to use the second method.

1. Select the target range in the worksheet.
2. And then click the tab “Data” in the ribbon.
3. After that, click the button “Text to Columns” in the toolbar. Thus, you will see the wizard window pop up.
4. For the first two steps in the wizard, click the button “Next” for two times.
5. And then in step 3, choose the option “Date”.
6. Next you need to choose the date format according to the original date format. Here the original numbers are in the format of YMD, so we choose this format.
7. Choose a destination for the result. You can also choose to input the result in the original range.
8. In the end, click the button “Finish” in the window.

And then the numbers will become dates in the worksheet. The date format is not YMD. This is because the date format in your computer is MDY. If you need to use other date format, you can also change according to your need.

You can see that using text to columns is quicker than the previous one. But both of the methods can help you finish your task. You can use either of them in your worksheet.

Focus on the Safety of Excel

With more tasks in your work, you will certainly have inputted a lot of data and information in Excel files. Therefore, you need to focus on the safety of Excel. Whenever you find Excel corrupts, you need to fix it immediately. Now you can use a third-party tool to repair damaged Excel file. With this tool at hand, you can make sure that your files are safe.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair corrupt doc and outlook repair software products. For more information visit www.datanumen.com