-
Jun 24th, 2021, 04:27 AM
#1
Thread Starter
Member
[RESOLVED] Excel VBA TimeSpan? Help
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
Last edited by swain90; Jun 24th, 2021 at 05:30 AM.
-
Jun 24th, 2021, 05:02 AM
#2
Re: Excel VBA TimeSpan? Help
Originally Posted by 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
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?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 24th, 2021, 05:27 AM
#3
Thread Starter
Member
Re: Excel VBA TimeSpan? Help
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
-
Jun 24th, 2021, 05:30 AM
#4
Thread Starter
Member
Re: Excel VBA TimeSpan? Help
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"))
-
Jun 24th, 2021, 05:37 AM
#5
Re: [RESOLVED] Excel VBA TimeSpan? Help
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 24th, 2021, 06:38 AM
#6
Re: [RESOLVED] Excel VBA TimeSpan? Help
Code:
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
Last edited by Arnoutdv; Jun 24th, 2021 at 06:49 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|