Excel date formats
 

Okay, so I know how to take a date that's entered as digits and have it automatically format to the text version. But what about the other way around? If I put "Oct 26, 2006" in a cell, I want it to automatically convert to 10/26/2006

Possible?

Juts go to format > cell, and click on "number." If you select "date," it'll offer you a wide variety of options, so that no matter what format of date you type in, it'll automatically format it to your desired arrangement.

You'd think that....but that only works for my first case, going from a numbers-only date format to any other date format. Doesn't work the other way around.

Hmm, check that. It seems to be working in a new blank document, but not in the document I need it to be working in. Grumble

I use open office exclusively now so it has been a while. Open Office makes that conversion automatically unless you tell it not to.

This isn't one of the standard date conversions available in Format --> Cells --> Numbers --> Date?

Pisser, it seems that it doesn't feel like recognizing the input format (over which I have no control) as a date.

The input format is

mmm dd yyyy hh:mmAM (where mmm is the 3 letter abreviation for the month)

which I'd like to convert to mm/dd/yyyy hh:mm

Unfortunately, it's looking for
mmm dd, yyyy hh:mm AM

Without that comma and the space before AM/PM, it doesn't think it's a date so it doesn't apply the date formatting. :mad:

Hmmm...wish I had a copy of Excel handy to poke around with. It shouldn't be difficult. But all I have in the house is Works, which is just Excel light and it is automatically translating "Oct 5, 1999" to "October 5, 1999" and accepting whatever date formatting I put on it.

For me, if I put "Oct 5, 1999" it happily converts it to whatever I want. But "Oct 5 1999" stays "Oct 5 1999"

You could always use a calcuation column to translate the input text structure to the correct structure for what Excel wants.

=LEFT(A1, 6) & ", " & LEFT(RIGHT(A1, 12), 10) & " " & RIGHT(A1, 2)



(Or something like that, that is all from my head and untested. This would convert Oct 5 1999 06:23AM to Oct 5, 1999 06:23 AM)