Changing date time formats in EXCEL

[gordo29]

I need a way for EXCEL to accept and convert a date/time in the following context:from yyyy MMM DD TTTT to mm/dd/yyyy TT:TT

Example: 2006 Mar 25 1650 to 03/25/2006 16:50

Thanks for the help.

GB

[ratcat]

Welcome

Right click on the cell go to Format Cells -> Number Tab -> Select Custom under the Category option box then find the format your looking for in the right hand side of the Format Cell box.

If there is no option type in the field in the type box

Please Login or Register  to view this content.

Then click OK.

It works in EXcel Office 2007 I can't not remember whether you can do it in the older versions.

Hope than helps

[gordo29]

Will try it tomorrow, Thank you very much (Elvis-style)

Gordo

[gordo29]

Version problems?

I am using 2003 version............Help!!


Gordo

[darkyam]

Your format is not being recognized by Excel as a time. You need either a formula or macro to convert it. I would suggest

Please Login or Register  to view this content.

, assuming your date is in A1. You could then apply a custom format of mm/dd/yy h:mm if you need the leading 0 in month or day.

[daddylonglegs]

Try this formula to convert

=REPLACE(REPLACE(A1,LEN(A1)-1,0,":"),5,5,"-"&MONTH(1&MID(A1,6,3))&"-")+0

format as required

[gordo29]

working 12 hr shifts, so we will have broken conversations. Will try these two.

Gordo