Convert Text :> Time... Using VBA

Hi all,

In a export of data i have a few colums containing time info as text in the following format...

1h 45m 25s
1h 6m 45s
17m 16s
1m 45s
42s
4m 28s
10m 44s
6h 8m 15s
10m 8s
4h 48m 56s
1m 33s
3h 52m 44s
2h 3m 33s


I want to know if there is a VBA script I can use to run through those columns converting the data to actual time... so stripping the "h" "m" "s" from it, and returning the data in something like "01:06:54" format. I've attached a "sample" spreadsheet, which is simply 1 small column of data. Note: Some entries are blank, as this best replicates the data I get, so a formula needs to be able to skip blank entries.



^^ OK, scratch that, I cant upload data like this from the office, so I'll need to do it when I get home a little later.... but it really is just...

Avg Response Time in Bhrs
29m 16s
1m 32s
30s
56m 39s
2h 9m 49s
8m 17s

22m 0s
5h 35m 37s
43m 32s
38s

1h 36m 36s
5m 45s
2d 20h 36m 40s
57m 29s
58s
43m 37s
2h 11m 8s
10s
2m 36s

3d 22h 2m 9s
1h 9m 51s

37s
1h 45m 25s
1h 6m 45s



Many thanks in advance...

Hey Guys,

Thanks for this, I'll test this today, if it works, sweet, I'll let you know. If not, I'll throw up a sample sheet and details of what happens.

OK, I'm really grateful for this, but if I'm honest I'm having a little trouble seeing how to use this function... I have a SUB already running to manipulate some other areas of the data... I really want to provide a sample, but its full of private customer data.... can I "call" this function? How do I indicate which columns/rows to run it against?

ahhhhhh awesome, I'll give that a go, thank you!

OK, superb! This works in the sheet as described! Thank you so much.

Is it possible for me to run through the column in VBA, under the following conditions...

Check if A2(then a3 etc, for as many as there is data for)
converting each one as it goes....


If this is too much, or not possible so far as you know I totally understand, and really appreciate what you've done for me so far.

OK, as a function this is working fine, i haven't tried to run it as part of script yet, but otherwise it's fine (I also modified the Module name, and it stopped working, that took me a few minutes to figure out!).

However now I'm working with a larger dataset, I can see that there are Mos (months) and W (weeks) in here too....

23h 18m 39s
3d 6h 52m 30s
13s
27m 22s
9m 24s
1mos 0w 4d 22h 56m 0s
1mos 0w 0d 17h 28m 30s
1w 4d 19h 54m 59s
1d 19h 50m 14s


Considering that "Mos" and "W" are just preset numbers of Hours, I can see a way to amend your function to include this... Does anyone have any thoughts?

Hi there kvsrinivasamurthy, thanks for the reply.

[h]:mm:ss is the preferred format. In a perfect world, it would depend on the month, but for the purposes of this report, 30 days would be fine in most cases.

Thanks Bo_Ry for that, it's the closest and has allowed me to get what I need.

Thanks to everyone that took the time to post, I'm sure many of these solutions were useable, but my own knowledge shortfalls have gotten in the way of my understanding :-(