Date Manipulation

[McDan1el]

Afternoon All,

Quick question (or two) for you - to help with some data manipulation - one of our system exports raw data which I need to manipulate, the first question is:

Is there a quick way to remove the " ' " symbole that excel likes to add to data sometimes, for instance:

The cell shows:

5/3/2011 12:00:00 AM

However when you click it - the formula box shows:

'5/3/2011 12:00:00 AM

If i delete the ' manually the cell turns to

05/03/2011 00:00

Which is all gravy - the exception being that the system we use exports in US date format, so that is meant to be May 3rd not March 5th... any way we can convert this to a UK date format?

Thanks guys

 

[Ron Coderre]

It's important that you post the version of Excel you are using so we can give you version specific solutions.
Try this...
• Select your single column range of "dates"
• Data.Text-to-columns
...Check: Delimited....Click: Next....Click: Next
...Check: Date...MDY
...Click: Finish

That should remove the apostrophes and convert the text to a MDY date

Does that help?

 

[McDan1el]

Apologies it's Excel 2003.

That worked for the bulk of the entries - but oddly enough some didn't convert over...

13/14/15/16/17/18th May all stayed as they were (it was data 1-18th) - but 1st-12th converted fine... I will play about some more!

 

[Ron Coderre]

The behavior your describing implies that the section containing numbers 13-31 is being referred to as a month in text-to-columns...not a day.

Example:
18/3/2011
21/3/2011

On the 3rd Text-to-Columns screen...Check: Date...DMY

Does that help

 

[McDan1el]

That's what I thought - but I am definately selecting MDY and it missed those which show as

05/13/2011 12:00:00 AM

it converts

05/12/2011 12:00:00 AM

to

05/12/2011 00:00

but changing the date format shows this as 5th December not 12th May - despite when doing Text > Columns i am 100% checking MDY