[RESOLVED] Excel VBA TimeSpan? Help

[swain90]

Hi,

I'm needing to convert a TimeTick value into a readable format, however I'm unsure how. Quick read around and it seems like this can be done via timespan, however I don't really understand how this works.

As example, i'll get a value of 805123 which equates to 2 hours 14 minutes 11.23 seconds. I would like to be able to convert 805123 value into a a breakdown of hours, minutes etc...

Thanks

Swain90

[Zvoni]

Hi,

I'm needing to convert a TimeTick value into a readable format, however I'm unsure how. Quick read around and it seems like this can be done via timespan, however I don't really understand how this works.

As example, i'll get a value of 805123 which equates to 2 hours 14 minutes 11.23 seconds. I would like to be able to convert 805123 value into a a breakdown of hours, minutes etc...

Thanks

Swain90

TimeSpan sounds more like something from DOT CRAP, and not VBA

How did you get that 805123 equals 02:14:11.230? Where did you find that? What's the Function/Algorithm used?

[swain90]

I'm retrieved the value of 805123 via SNMP as that was the up time of the device in TimeTick value.

From reading further I can multiple the timetick value by 0.01 which gives me the time in seconds, however I'm struggling to format that time in seconds into hh:mm:ss


uptime = objSNMP.Get(".1.3.6.1.2.1.1.3.0") 'this is the SNMP TimeTick value
Dim uptime2 As Long

uptime2 = uptime

uptime2 = uptime2 / 86400

MsgBox (Format((uptime2), "hh:mm:ss")) 'this just returns 00:00:00

[swain90]

Got it working!

Dim uptime2 As Long

uptime = objSNMP.Get(".1.3.6.1.2.1.1.3.0")

uptime2 = uptime

uptime2 = uptime2 * "0.01"




MsgBox (Format((uptime2 / 86400), "hh:mm:ss"))

[Zvoni]

If it's really a Tickcount, then that value would be the passed time (uptime?) in milliseconds (and yes, i know the value is fuzzy for some 15ms).
So in your case it would be
805123 / 1000 --> 805.123 seconds

or better:
805123 \ 1000 = 805 (seconds)
805 \ 60 = 13 (minutes)
805 mod 60 = 25 (remaining seconds)

Result:
0h:13m:25s.123ms

Or in VBA using Function TimeSerial to combine the separate units to a timevalue

[Arnoutdv]

TimeTick = 805123
Debug.Print CDate((TimeTick / 100) / 86400) 
-> 02:14:11
Debug.Print Format((TimeTick / 100) / 86400, "HH:NN:SS")
-> 02:14:11

/edit I missed post #4, in which TS says he got it working