Home / Formulas / Text not Converting to Date

Text not Converting to Date

The ExcelHelpDesk Support team received the following request for help

Problem – Text not Converting to Date

Dear reader, I have been trying to switch the 1900 checkbox on and off to enable correct conversion of my date, written as 100470, to 10-04-1970, but excel keeps changing it to 27-01-2175.
I made a list with hundreds of these birth dates so really would like help on how to force excel to convert it properly.
thanks in advance
kind regards

For this problem we received a copy of the workbook. We then used a formula to convert the original birth date text into a “DateValue” and finally format those cells as dates to create a column with the actual Birth Dates.

Sample Dates as Text and not Converted

See below an example of the data provided for conversion

Step 1 – Include DATEVALUE Formula

Now before we can get a converted date we need to convert the text in column A into a datevalue. Once we have a datevalue we can then format that column into a date and we will have the converted date values.

Starting in cell D2 we have the formula

Make sure D2 is “General” format before including the datevalue function. This formula uses a combination of functions that result in a datevalue. See the resulting datevalue for the formula below.

Step 2 – Now Format Date

Now using the standard format cells simply change the format of the cell D2 to be a date in your required format and the datevalue will be converted to that format.

Once the format of the cell is changed to a date the datevalue is displayed in the correct format. With cell D2 now changed simply drag that formula down to convert the remaining dates into the correct format. Here is the resulting converted list.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment

About Excel Help Desk

Check Also

INDIRECT to extract a value from another worksheet

You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.