I have found myself building and modifying a lot of complex spreadsheets for work and unfortunately sometimes taking work home. To insure proper compatibility between formulas and Macros written at home for use at work I bought Office 2010 for home, because that is what I have at work. I have a column which needs to be divided into multiple columns " 10/25/2015 12:00:00 AM - 12:30:00 AM " needs to become "10/25/2015" " 12:30:00 AM I have been using a macro that uses "text to columns fixed width" to divide the column into three columns, however; my Macro breaks later in the year when the date gets to 10 characters: 10/9/2015 works but 10/10/2015 does not work. My solution to this was to use "Text to columns Delimited" If I break up the columns based on spaces, Excel removes the AM from 12:00:00 turning midnight into noon. If I am at home I can split the columns in two at the "-" which somehow turns 12 hour time into 24 hour time "10/25/2015 00:00:00" "00:30:00" I can then split the cells again by the spaces to get "10/25/2015" "00:00:00" "00:30:00" I can live with 24 hour time and this works regardless of the date length. Sadly when I take the Process to my work computer everything works fine until the 2nd splitting where Excel for reasons unknown turns "00:00:00" into " "12:00:00 PM". I have tried looking in my settings for anything that would explain the inconsistency but I have not found anything.
Hopefully someone here has run into and solved problems like mine, I would also be open to another way to handle splitting this data if there is one.
P.S. both computers are running Windows 7 pro
Bookmarks