Convert MMM DD, YYYY hh:mm:ss to Short date in excel 2007

[harsh2209]

I have an Excel report that I import from IBM Clear Quest tool (Web App). A field, 'Submit_date' in this report has data in the following format:

'Jan 12, 2012 12:00:00 AM'

If I double-click in the cell the cursor is after the AM. When I click outside of the cell, the date time stamp changes to 1/12/2012.

My question is, how to change the entire column so that all data gets converted to short date (mm/dd/yyyy) and thereby making it a data sortable field.

Appreciate your help very much!

[Dennis7849]

Standard cell formatting should work on this. Click on the column label(not a cell), then right-click inside the selected column and choose format cells. Select whatever formatting you want.

[harsh2209]

Hi Dennis, I tried your suggested route but, it didn't work. FYI - When the excel is imported, the field has 'General' formatting.

[ATLGator]

Try multiplying each cell by 1 (e.g., =A2*1), and then make sure it is formatted as a date. You can multiply it by 1 in another column and then copy/paste special/values back to the original column

[ATLGator]

Actually, if you have single quotes around both sides of the date, try this instead:
=(LEFT(A2,LEN(A2)-1))*1

Then, copy/paste special/values back into the original column.

[daddylonglegs]

Try using Text to columns

Select columns of dates then Data > Text to columns > OK

That should convert all dates - now format as required

[harsh2209]

Hi ATLGator, your suggested solution (multiplying by 1) did work for me.

One question - Do you know why the 'Format Cells' doesn't change such 'General' data..?

[harsh2209]

Hi daddylonglegs, your suggested solution is also very helpful! Thanks!

[daddylonglegs]

Do you know why the 'Format Cells' doesn't change such 'General' data..?

Your dates are formatted as text - you can only use formatting to change the appearance of numeric data (like real dates) - formatting can't change text-formatted values to numbers or vice versa

[darknation144]

Do you still need any help?

[mangesh.chouhan]

It is not working for me 9/27/2012 12:00:00 AM this needs to be converted in dd/mm/yyyy format. I tried above solution but not successful.

[mangesh.chouhan]

Hi,

Need to convert 9/27/2012 12:00:00 AM in dd/mm/yyyy format....tried text to column, format cells, formula =(LEFT(A2,LEN(A2)-1))*1 but not working....

[arlu1201]

Mangesh,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.