Text date converted to numerical date

[Sonia10]

I have a text date, JUL/02/10. How do I convert to a numerical date? This is not a format issue.

 

[Rick Rothstein]

I have a text date, JUL/02/10. How do I convert to a numerical date? This is not a format issue.

Do you want a formula in order to convert it in a different cell? If so...

=0+SUBSTITUTE(SUBSTITUTE(A1,"/"," ")," ",", ",2)

 

[Sonia10]

I have a text date, JUL/02/10. How do I convert to a numerical date? This is not a format issue.

Thank you. This was the perfect solution!

 

[Fluff]

Another option.
Select the column with the dates, On the Data tab select Text to columns > Delimited > Next > clear all checkboxes > Select MDY (next to date) > Finish

 

[Rick Rothstein]

Another option.
Select the column with the dates, On the Data tab select Text to columns > Delimited > Next > clear all checkboxes > Select MDY (next to date) > Finish

It is probably a locale "thing", but that did not work for me... I had to physically replace the slashes with spaces first, then that procedure worked. If the OP wants to do that conversion within the existing cells, here is the step-by-step instructions for the procedure I had to follow...

1) Select the column with your text dates

2) Bring up the Replace dialog box (shortcut... press CTRL+H)

3) Put a forward slash in the "Find what" field, a single space in the "Replace with" field

4) Click the "Options>>" button and make sure the "Match entire cell contents" checkbox is NOT checked

5) Click the "Replace All" button

6) Now, with the cells still selected, bring up the "Text To Columns" dialog box ("Data" tab, "Data Tools" panel on the Ribbon)

3) Select the "Delimited" option button and then click the "Next" button

4) Make sure the "Space" checkbox is not checked and then click the "Next" button

5) Select the "Date" option button and select "MDY" in its associated dropdown box

6) Click the "Finish" button (you can now format the cells to make the date look the way you want)