# Convert Hours minutes and Kilometer text values to numeric

1. ## Convert Hours minutes and Kilometer text values to numeric

Good Morning,

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.

2. ## Re: Convert Hours minutes and Kilometer text values to numeric

Originally Posted by GrayWolf
"68.67 km" convert to 67
Jut to clarify, is this a typo? Wouldn't 68.67 round to 69?

BSB

3. ## Re: Convert Hours minutes and Kilometer text values to numeric

You could do this with formulas if you put your data into a table. Failing that, here's a VBA solution that would ignore previously converted cells:

``Please Login or Register  to view this content.``
WBD

4. ## Re: Convert Hours minutes and Kilometer text values to numeric

Sorry yes. Typo should be 69. i've corrected it.

5. ## Re: Convert Hours minutes and Kilometer text values to numeric

Originally Posted by WideBoyDixon
You could do this with formulas if you put your data into a table. Failing that, here's a VBA solution that would ignore previously converted cells:
WBD
Thank you very much for this solution. I tested it on 10,00 rows and it worked great, I tried it on 100K rows, took a few minutes, tried it 3 times on 600K rows and I think I broke excel, LOL.
As far as I am concerned this is perfect.

EDIT: Just to note: I was converting the entire ranges. When I just convert the newly added range, just a second or 2 every time. Even with 600K rows, just a few seconds to convert the newly added records.

Could I ask you to put some comments in this so I can figure out what it is doing, in case I need to tweak it at all.

Thank you very much.

Rep'd

Gray

6. ## Re: Convert Hours minutes and Kilometer text values to numeric

Well done to WBD for a great solution.

I'd be tempted to take it a step further. If a human is manually copying and pasting the data in from elsewhere (presumably a CSV file or similar) then you could add to the code to automate that too. At the point the new data is tagged onto the end of the existing data you'd know what the first and last row of new data is and you could run WBD's code on just that section rather than the entire dataset. That would make things run very quickly every time.

Just a thought.

BSB

7. ## Re: Convert Hours minutes and Kilometer text values to numeric

Well done to WBD for a great solution.

I'd be tempted to take it a step further. If a human is manually copying and pasting the data in from elsewhere (presumably a CSV file or similar) then you could add to the code to automate that too. At the point the new data is tagged onto the end of the existing data you'd know what the first and last row of new data is and you could run WBD's code on just that section rather than the entire dataset. That would make things run very quickly every time.

Just a thought.

BSB
I agree with you BadlySpelledBuoy, and since I am not doing this myself, that crossed my mind a few times.
There is some extraneous data (always in the same format) that is not required in the export. The export range is never the same size. Plus, they might not be pulling the report daily. They might take a day and pull 7 reports for the previous week. hence the reason I thought to just let them copy and paste, then enter the date in manually.
If you have some ideas though. I am happy to entertain anything that will make their job easier.

Hope that makes sense.

Gray.

8. ## Re: Convert Hours minutes and Kilometer text values to numeric

None of that sounds like it couldn't be automated.

If the extraneous data data is always in the same format then it's identifiable through code regardless of where it sits in the data, which means it can be deleted prior to import or simply ignored when copying and pasting with code.

If they may not extract the report daily then you could have a simple "processing" folder where each of the multiple reports are saved temporarily. The code could loop through each file in that folder, check it's actually one that needs to be imported then import it before parking it on a "processed" folder. There are other ways this can be dealt with too, such as appending a flag to the file name to show it's already been preocessed and therefore ignored on the next run.

The date can obviously be added through code.

Automation of tasks like this (or human error proofing as I like to call it) is a fair chunk of my day job so I've found many ways over the years to turn long winded manual processes into "click a button" exercises.

BSB

9. ## Re: Convert Hours minutes and Kilometer text values to numeric

OK. On reflection you can only examine the pasted rows by responding to Worksheet_Change()

``Please Login or Register  to view this content.``
WBD

10. ## Re: Convert Hours minutes and Kilometer text values to numeric

I just noticed that the decimal in the numbers is not actually a decimal.
I originally was thinking that it extracted the decimal of a minute and not putting a . in place of :

Example:
"01 hrs 23.00 min" is 1 hour and 23 minutes and 0 seconds
"41.24 min" is 41 minutes and 24 seconds
"23.50 min" is 23 minutes and 50 seconds
"01 hrs 28.21 min" is 1 hour and 28 minutes and 21 seconds

Also the percentage is messed up as well. I am not sure why the export places the leading 0.00 on the value. That's just weird.
I need to figure out how to fix these within the loop also.
0.0009% should be 9%
0.0072% should be 72%
0.0003% should be 3%
0.0024% should be 24%

Gray

There are currently 1 users browsing this thread. (0 members and 1 guests)