A.N.M. Mohaimen Shanto
Working with Microsoft Excel’s Date format is very common. We use them for various calculations. The Date is one of the essential formats in Excel. Excel is smart enough to convert General format to Date format. But there may be situations where dates are in General or Text format. In this tutorial, we will show you how to convert the date from General format to Date format in Excel with suitable examples and proper illustrations.
Download Practice Workbook
Download this practice workbook.
7 Useful Ways to Convert General Format to Date in Excel
Here, we are providing you with 7 useful and effective methods to convert General format to Date format. We recommend you learn and apply all these methods to your dataset.
Note: General format in Excel means no specified format. Whenever you enter a numeric or alphanumeric value in a cell, Excel counts them in General format. That is why in this tutorial, we will convert text and numbers along with general format to Date format.
1. Error Checking Option in Excel to Convert General to Date
Sometimes your Date-data might be in General format. It can happen because of misuse of the data formats. It will show an error sign beside the cells.
Before applying this method, make sure that the Error Checking Option is enabled.
We are using Excel365. In order to enable Error Checking Option:
1. Click on File > More > Options.
2. Select Formulas.
3. In Error Checking, check the Enable background error checking box.
Now, take a look at the dataset:
Here, the dates are in General format. To identify the problem, click on any cell. You will see a box, showing an error sign beside the cell.
Now, follow the steps below.
📌 Steps
1. Click on the error-indicating box.
It shows that the text date has a two-digit year. That’s why this is not in date format. To solve this,
2. Click one Convert XX to 20XX option.
As you see it converted the general format to text format.
3. Now, select the rest of the cells and select Convert XX to 20XX option.
It will convert all those to date format.
2. Number Format Option in Excel to Convert General to Date
Here, we are using the same dataset. But our method is different. We will convert them to Date using the different Number format options available in the Home tab or in the Format Cells window in Excel. Let’s see the steps below.
📌 Steps
1. First, select the range of cells B5:B10.
2. From the Home tab, go to the Number group. Click on the expand arrow. After that, the Format Cells dialog box will appear.
3. Now, select Date from Category. In the Type option, you will see different types of date formats. Choose a suitable one.
4. Press OK.
As you can see, we are successful in converting general format to date format in Excel.
3. Excel Paste Special Option to Modify General to Date
Now, we don’t use this method too often. But it can convert text format to date format. We are using this method for the following dataset:
📌 Steps
1. First, copy any empty cell.
2. Now, select the range of cells B5:B8.
3. Now, Right-click the selection, click on the Paste Special. After that, Paste Special dialog box will appear.
4. Now, select the radio button Add.
5. Then, click on OK. It will convert them to General format.
Excel converts a text string to a number and adds a zero which does not change the value. You get the date’s serial number in General format.
6. Now, we will format it like the previous method. From the Home tab, go to the Number Click on the expand arrow. After that, the Format Cells dialog box will appear.
7. Then, select Date from Category. In the Type option, you will see different types of date formats. Choose a suitable one.
8. Press OK.
As you can see, we have converted the text strings to date format.
Similar Readings:
4. Find & Replace Command to Switch General to Date in Excel
Now, this method will not work for every General format or Text format. We will replace a particular character with the slash (“/”) character. Then it will automatically convert it to Date format.
To demonstrate this method, we are using this dataset:
📌 Steps
1. First, select the range of cells B5:B9
2. Then, press Ctrl+F on your keyboard.
3. Select Replace option.
4. In the Find What box, type dot(“.”), and in Replace with box, type slash(“/”).
5. Click on Ok.
As you can see, this command has converted our dataset to date format.
5. Text to Columns Wizard in Excel to Convert General to Date
Now, this method will work only for limited types of general formats. To demonstrate, we are going to use this dataset:
📌 Steps
1. First, select the range of cells B5:B8.
2. Go to the Data tab. Select Text to Columns option
3. In the dialog box, select the Delimited radio button. Then, click on Next.
4. In the delimiters option, uncheck all the boxes. Then, click on Next
5. In the Column data format, select Date. And from the dropdown select any of the options. We are using the DMY format.
6. Click on Finish.
As you can see, we are successful in converting general format to date format.
6. The VALUE, DATEVALUE, and DATE Functions to Convert General to Date
Now, in this method, we are using functions to convert the General to Date format. These three functions will work efficiently to solve your problem. Make sure you click those links to know more about them.
6.1 Using the VALUE Function
The VALUE function converts a text string that represents a number to a number. You can also use it to convert the general to date format.
Syntax:
text: Required. The text is enclosed in quotation marks or a reference to a cell containing the text you want to convert.
To demonstrate this, we are going to use this dataset:
📌 Steps
1. Type the following formula in Cell B5.
2. Then, press Enter.
3. After that, drag the Fill Handle icon over the range of cells B6:B8.
As you can see, we have converted the general format to the date format successfully.
6.2 Using the DATEVALUE Function
Now, the DATEVALUE Function converts a text date into the date-time number codes which are in General format. Then you have to format it with the Number Format options like the previous method we’ve shown earlier.
Syntax:
You have to pass the cell reference in the DATEVALUE function. Take a look at this dataset to understand this better:
Just change the Result column format by using the Format Cells option.
After that, you will be able to see the date in the actual date format.
6.3 Using the DATE Function
The DATE function returns the sequential serial number that represents a particular date. We will use this function together with the RIGHT function, the MID function, and the LEFT function.
Syntax of the DATE function:
The Generic formula of this method:
The following screenshot demonstrates a few examples of this method in action:
All you have to do is change the cell references and number of characters in the functions.
7. Mathematical Operations to Turn General to Date in Excel
Now, you can perform a simple mathematical operation to convert the General to Date format. But remember, you have to do this operation without changing the actual Date values. So that, your actual date remains as the Text date. You can do addition, multiplication, division, or double negation to convert.
Operations like these will do it for you:
=text+0
=text*1
=text/1
=–text
The following screenshot will give you a clear idea about this method:
If the value is in date format already, you don’t have to do these operations.
💬 Things to Remember
✎ Microsoft Excel stores date since January 1, 1900. So, the use of the Excel DATEVALUE function on earlier dates will show #VALUE! Error.
✎ The DATEVLUE function cannot convert numeric values to dates. It converts text dates to actual date format. For this reason, use the VALUE function.
✎ If you find this methods complex, try to convert dates using the Number Format group in the Home tab. This should be your go-to method in the first place.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about converting general to date format in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
