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