Excel date formats

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:56 PM	#1
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	#2
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	#4
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