I was wondering if someone can help me with this data output.
Each day a human will manually export the data and paste it into a spreadsheet then add the date to column A. Then the next day they will paste the newly exported records at the bottom of the list. This will continue day after day and year over year.
I was hoping someone would have a macro solution they could run after pasting the data each day that loops through the records and does the conversions.
I have a little bit of VBA understanding. If the code could be commented so I can maintain it if necessary, I would appreciate that.
I originally started doing this with formulas but, after some discussion with the end users, by the end of the year this could have over 100,000 rows, pivot table reports and formulas. They want to use this for a year over year reporting so after a few years we could be looking at 100K rows every year.
I figured that would make this workbook very slow. I realize that even after a couple years this might not be optimal. Excel is their only option at present.
Basically what I am looking for is a way to convert the values in columns D, E, F, H from hours and minutes text value to the rounded off numeric value of total minutes.
The values from the export look like this along with what I would like it converted to:
( i did notice that values with minutes only are 9 characters long and values with Hours and minutes are 16. It never varies 9 or 16)
"01 hrs 23.00 min" convert to 83
"41.24 min" convert to 41
"23.50 min" convert to 24
"01 hrs 28.21 min" convert to 88
Likewise column "i" will need to be converted to just the rounded kilometer number.
"157.56 km" convert to 158
"0 km" convert to 0
"202.67 km" convert to 203
"68.67 km" convert to 69
Please let me know if you require further clarification.
I really appreciate everyone's time and input on this matter.