First of all, understand that "proper" Excel "dates" (including "time") are not dates at all. they're numbers. they are actually the number of days since 1/1/1900 and "time" is he fractional part of a day.
so, January 10 of 1900 at 6:00am is actually 10.25 What you/we SEE is the "display format". So, we can display this number in any way we want, including the UK date format. And, since the dates are really numbers we can use them in calculations. date completed - Date received gives you the number of days to complete. Today's date (=Now()) + 7 will give you the date 1 week from today... However, in your case (because of the apostrophe) you don't have a DATE, you have a TEXT STRING. So.. What *I* would do is: Copy the cell(s) to the adjacent column. Remove the apostrophe (find/replace) This will convert the cell to a "proper" Excel date. Then, simply change the display format. Now, it's possible that the date string you have isn't what Excel considers a "proper" date format, so it won't convert it. In which case, we have to do more.. Now, if you really want the new cell to be a text string instead of a date, then there are a few more steps... but your said "two adjacent columns"... why two? do you want them to BOTH be the UK date? can you send me a file of just the column of dates and I'll see what I can do? Paul ________________________________ From: Gerry <gmccaff...@acutus.co.uk> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Mon, March 15, 2010 7:28:13 PM Subject: $$Excel-Macros$$ My dates are stored in various text formats in single column...Clever solution required. I have two columns with 16300 rows of data. The contents of the columns all start with an apostrophe (which is not seen in the cell itself, but only in the entry bar). The dates are either 'mm/d/yyyy or 'm/d/yyyy or 'mm/dd/yyyy or 'm/dd/yyyy. Some of them even have a trailing hh:mm:ss at the end. (Some of them are blank too) I would like to place two adjacent columns containing the proper date data in the UK format dd/mm/yyyy. I am not interested in the hh:mm:ss part. If blank, I'd like to leave the blank intact in the newly formulated column. Any help would be most appreciated. Thanks in advance Gerry -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
