I have a cell in general format with dates looking like this: Tue, Oct 26, 2010 and want excel to recognize it as a date. How do I do that? Thanks!
I need to frequently update the original data and would therefore like a formula that translates the text into a date rather than simply changing format... Thanks again
Thanks for the inputs, C Masters. This is close to what I have tried and I know it's pretty much what I'm looking for. However, I always get a #Value error.
I assume this is because of the three letter month name (e.g. Oct ) instead of number.
Does somebody know how to fix this?
Right click on the cell, go to “format cell”. Go to the “number” tab, and select the format of which you want to use.
**EDIT** I test ran the samples below with the “Tue, Oct 26, 2010” you supplied above and got a good result. I did get #VALUE! once, but that was because I had nothing in cell A2 which is where the formula was looking for the original text string…
try diagnosing by seeing what gets returned from =(MID(A2,FIND(“,”,A2)+1,100))
maybe there is a double space or something that is throwing off the rest of the formula
compare the values from =LEN(A2) and =LEN(TRIM(A2)) ??
if the text string was in A2
this assumes your data entry is constant with a 3 letter weekday abbreviation+comma+space before the month, and returns the date as the number that Excel uses to track dates off of 01/01/1900
is little more flexible because it looks for the first comma in the cell and takes the remainder of the cell contents to the right
you’ll still need to set the cell formatting to one of the Date options if you want it to display as something like 10/26/2010 instead of 40477
if you want it to display directly into a standard date format you could do something like
hope that helps