Hi. I've recorded a macro that should do text to columns on a date column. The dates are formatted 05/05/2011. When I recorded the macro I specified that the first three characters were to be discarded (so leaving 05/2011, which Excel then automatically takes to be 01/05/2011 - It strips the day out of the date so that all the dates fall on the first of their respective months). This is the macro that Excel came up with:
However, when I run it, it returns gibberish. 25/09/2012 should become 09/2012 (so 01/09/2012 in the cell) but it becomes 01/05/2012. I can normally record and fix a macro but this is just beyond me! I think I have identified what the problems are but I can't fix it. I can see two problems: Firstly, Excel (in the macro) seems to ignore all of the 0's in the cell (so in my example I think Excel takes 25/09/2012 to be just 25/9/2012). Secondly, I think despite the date format being the UK version (dd/mm/yyyy) I think the macro reads it as mm/dd/yyyy so 25/09/2012 becomes (after Excel has removed the zero) 9/25/2012. The macro recorded me stripping out the first three characters so it goes on to strip out 9/2 leaving just 5/2012 (which becomes 01/05/2012). I think therefore I'm looking for help on two counts: 1) to stop excel missing out the zeros in the macro and 2) to force the macro to read the date as dd/mm/yyyy not mm/dd/yyyy.Please Login or Register to view this content.
Any help would be muchly appreciated!
Thanks
Bookmarks