Dates - AArrrhhhggg

[neville]

My eternal nemesis.

I am running excel 2007. I am converting American dates to proper dates and for a while my formulas of Right, Mid & left worked I just concatenated them.
Once that was done I could not format the column to the date format I wanted DD-MMM-YY, so I copied the entire column over one and pasted as values. That also did not work.

Is there a way in excel to force a format in a column??

Rgs

Neville

 

[Andrew Poulsom]

Have you tried Data|Text To Columns specifying Date MDY at Step 3?

 

[VoG]

Try this. Select a column of dates, Data > Text to Columns, click Next twice. Tick Date, select MDY then click Finish.

 

[neville]

Yes I have done all of the normal things for re-formatting. I have even copied the column into notebook and copied it back - failed on that too.

 

[neville]

When my right, mid, left failed I resorted to Text to columns.

 

[Richard Schollar]

So what do you currently have at the moment in each cell and what do you want it to be? In all instances where I've needed to convert mm/dd/yyyy text 'dates' into dd/mm/yyyy proper dates, Data>TextToColumns has worked every time.

 

[neville]

It is trying to get 02/11/2011 to look like 02-Nov-11. I know I am being a little pedantic but I was doing this for some work colleague and I cannot make it look like that and I would just like to see where I went wrong.

Cheers.

 

[Richard Schollar]

That's not an American date format - I am now confused. Have you generated the 02/11/2011 using your formulas or is this value what you have in the spreadsheet (before you do anything to it, formula or otherwise)?

 

[neville]

Hi Richard,

The American date thing I have dealt with. It is now the formatting issue I am struggling with. I know its basic excel but it has stumped me at the moment.

 

[Richard Schollar]

Presumably this is because you have generated a string that looks like a date rather than being an actual, numeric Excel date.

You should just be able to convert into a proper Excel date eg by formula such as:

=A1+0

assuming your string date is in A1. You need to format this formula cell to your desired date format for it to be displayed correctly.