Excel date formats

Ghoulish Delight · 10-26-2006, 02:35 PM

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?

LSPoorEeyorick · 10-26-2006, 02:46 PM

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.

Ghoulish Delight · 10-26-2006, 02:47 PM

Quote:

Originally Posted by LSPoorEeyorick

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

Alex · 10-26-2006, 02:47 PM

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?

Ghoulish Delight · 10-26-2006, 02:56 PM

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.

Alex · 10-26-2006, 03:03 PM

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.

Ghoulish Delight · 10-26-2006, 03:07 PM

Quote:

Originally Posted by Alex Stroup

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"

Alex · 10-26-2006, 03:08 PM

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)

10-26-2006, 02:35 PM	#1
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	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? __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

10-26-2006, 02:46 PM	#2
LSPoorEeyorick scribblin' Join Date: Jan 2005 Location: in the moment Posts: 3,872	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.
	Submit to Quotes

10-26-2006, 02:47 PM	#4
Alex . Join Date: Feb 2005 Posts: 13,354	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?
	Submit to Quotes

10-26-2006, 02:56 PM	#5
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	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. __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

10-26-2006, 03:03 PM	#6
Alex . Join Date: Feb 2005 Posts: 13,354	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.
	Submit to Quotes

10-26-2006, 03:08 PM	#8
Alex . Join Date: Feb 2005 Posts: 13,354	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)
	Submit to Quotes