PDA

View Full Version : [SOLVED] Manipulating a string in the form 11:22:33



gersemale
09-23-2005, 07:20 AM
Hi,

I have a string in the format HH:MM:SS and require to format it to minutes.

EG: 10:30:30 = 630.5min

I have tried using TimeValue(myDate) *24 *60 which works fine except the format is not 24 hour clock format, and does not stop at 24.00.00. Thus for values such as 28.00.33 the above method will return an incorrect minutes value.

The last solution I can think of would be to tokenise the string using the delimiter ":" to break it up.

How do you do this in VBA?

Thanks,

ger

BlueCactus
09-23-2005, 07:33 AM
This would be a brute force way of doing it:
myTime = "10:30:30"
myMin = Val(Left(myTime, 2)) * 60 + Val(Mid(myTime, 4, 2)) + Val(Right(myTime, 2)) / 60


There may be some more suitable VBA functions to increase the elegance somewhat.

gersemale
09-23-2005, 08:15 AM
Works a treat! Thanks BlueCactus

ALe
09-23-2005, 08:35 AM
I found this code very useful.
I had to work with big hour numbers so I modified BlueCactus code to work also for big hour number such as 100:00:00.

Here my solution in case you'll have my same need


myTime = "100:30:30"
mymin = Val(Mid(myTime, 1, (InStr(myTime, ":") - 1))) * 60 + Val(Mid(myTime, 4, 2)) + Val(Right(myTime, 2)) / 60