String to Date Conversion

How can I interpret this algorithm:

Wed Nov 17 01:44:07 +0000 2010

so that Excel will recognize it as a date & time.

Format Cells =>Number => custom ...then what do I enter????

Please help ASAP!!!!!!!!!!!!!!!!!

What about this:
=DATEVALUE(MID(A1,5,3)&" "&MID(A1,9,2)&", "&RIGHT(A1,4))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

Ensure that all entries follow the same format. All variables are on the same spot, that the month and day is always 3 and 2 char respectively; even if is September 6, it will be Sep 06, etc.

Welcome by the way. Take some time to read the rules. Your post's tittle should describe your problem and not the desired solution

You could shorten that given coercion etc and known string lengths:

=(MID(A1,5,6)&", "&RIGHT(A1,4))+MID(A1,12,8)
format as date

(the only unknown is dd or d but the above should account for either/or)

no chance! it workd the other way around only
the date value which is a number can be formatted to display in different ways.

The custom number format section is to format dates. Excel does not recognize your string as date but as a text string, so in order to convert it to a date you'll need a formula. Once excel has a date; then you can use the custom format and show it whichever way you see fit. Hope this made it clear why you cannot format your text string.

Originally Posted by dasreinfeld

that makes sense now...could someone walk me through the steps necessary to make this happen?

Well, I'd insert a column after your date-text-string column and convert it to an excel proper date using DKO's formula above, copy this new column and paste values to your original column, and then delete this helper column.

Then you can use Custom format if you don't see an option under Date that satisfies your demands.