Does it appear like that with the quote marks?I have a date that is text, meaning that when I copy/paste it from the internet it can not be formatted. Does anyone know a formula that can leave it so that it can be formatted?
The dates appears like this, "Oct 31, 2015".
Select the cell containing it (or if there are more than one cell, select the column containing the text dates), call up the "Text To Columns" dialog box (Data tab, Data Tools panel), select Delimited on the first dialog page and click the Next button, then uncheck all Checkboxes on the second dialog page an finish off by clicking the Finish button. All of your text dates should now be real dates which you can format with the date or custom format of your choice.No without the quote marks.
If you want to go with a formula solution, this is much simpler...Thanks guys. I'll go with steve the fish's formula and explore Rick's way over the holidays. This Excel is like a good Pandora's box, hahaha.
If you want to go with a formula solution, this is much simpler...
=0+A1
The cell format may indicate Text... ignore that and just format the cell with the date format of your choice.
With all the things Excel does for us automatically, one would think Excel could figure out the date from the month name, a 4-digit year and a day value (positive number less than 32) no matter what order they appeared in. Okay, what about the DATEVALUE function, is it smart enough to figure it out? I can't test it on my system, but does this work on your system...Excel wouldn't understand that on my version presumably because im in the UK. It would be ok for 31 Oct, 2016