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.
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.
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.
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?