How to Convert General Format to Date in Excel (7 Methods)

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.

error checking option for date in excel

Now, take a look at the dataset:

dataset to convert general to date in excel

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.

error in the date value in excel

Now, follow the steps below.

📌 Steps

1. Click on the error-indicating box.

date format problem in error boxIt 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.

convert to date from general in excel

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.

excel convert general to date

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.

select the range of cells contain general format

2. From the Home tab, go to the Number group. Click on the expand arrow. After that, the Format Cells dialog box will appear.

Number group in home tab

3. Now, select Date from Category. In the Type option, you will see different types of date formats. Choose a suitable one.

choose any suitable date format

4. Press OK.

general are converted to date

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:

dataset to convert general to date in excel

📌 Steps

1. First, copy any empty cell.

copy any empty cell

2. Now, select the range of cells B5:B8.

select the range of cells to paste

3. Now, Right-click the selection, click on the Paste Special. After that, Paste Special dialog box will appear.

paste special command

4. Now, select the radio button Add.

select the radio button add

5. Then, click on OK. It will convert them to General format.

date in general format in excel

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.

choose any suitable date format

8. Press OK.

general format converted to date in excel

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:

dataset for convert general to date in excel

📌 Steps

1. First, select the range of cells B5:B9

select the range of cells

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(“/”).

replace dot with slash

5. Click on Ok.

general format converted to date

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:

dataset to convert general to text

📌 Steps

1. First, select the range of cells B5:B8.

select the range of cells

2. Go to the Data tab. Select Text to Columns option

select text to columns option from data tab

3. In the dialog box, select the Delimited radio button. Then, click on Next.

select delimited option

4. In the delimiters option, uncheck all the boxes. Then, click on Next

uncheck all the box in delimiters

5. In the Column data format, select Date. And from the dropdown select any of the options. We are using the DMY format.

select DMY format for date

6. Click on Finish.

general converted to dates

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:

=VALUE(text)

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:

dataset for convert general to date in excel

📌 Steps

1. Type the following formula in Cell B5.

=VALUE(B5)

type the VALUE function to convert

2. Then, press Enter.

general to date format in excel

3. After that, drag the Fill Handle icon over the range of cells B6:B8.

drag the fill handle to copy

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:

=DATEVALUE(date_text)

You have to pass the cell reference in the DATEVALUE function. Take a look at this dataset to understand this better:

the DATEVALUE function to convert general

Just change the Result column format by using the Format Cells option.

choose any suitable date format

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:

=DATE(year,month,day)

The Generic formula of this method:

=DATE(RIGHT(text,num_char),MID(text,start_num,num_char),LEFTtext,num_char))

The following screenshot demonstrates a few examples of this method in action:

The DATE function to convert general to date in excel

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:

mathematical operations to convert general to date

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.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo