Date in American Format

Date in American Format

Didn't find your answer?

A client has e-mailed me a csv and some of the dates are in UK format ie 10/11/2011 and others are in US format ie 11/15/2011. How can I convert the american ones in to the UK format.

Thanks

Replies (3)

Please login or register to join the discussion.

avatar
By Richard Willis
21st Nov 2011 17:25

It depends!

If the dates are stored as Excel Date Numbers then simply by highlighting all the dates and selecting format cells and a UK type format should sort them all out.  IF, however, they are text then more giggery-pokery will be required.

Thanks (0)
avatar
By Cantona1
21st Nov 2011 19:10

Date in American Format

If the above does not work, you could use the following:

highlight the data: Text to columns:Delimited: choose, a Hyphen, or comma separator: Next: You see Column Data Format: Tick the date option, then from the drop down menu choose: DMY:

If this does not work, you can use a string function, such as Left,Right, and Len functions. I had similar problem years ago, Richard had came up with a solution. I do not know where Richard is now. He was one of the excel expert in this forum.

 

Thanks (0)
By George Attazder
21st Nov 2011 22:22

The correct answer...

... is to send it back to the client and get him to sort it out.  He doesn't want to pay you to do it surely?

PS. What makes you sure that 10/11/2011 isn't US format?

Thanks (0)