Not sure where the methods that you are using are coming from. It sounds like you have a type conversion issue, the methods that you are using are causing the rounding for the time format, but I would be tempted to treat the dates as floats, then you should see a higher level of accuracy. The number is in seconds so easy enough to convert to minutes ( /60), hours ( /60), days (/24).
I tried treating the date value as a float but I don't think the values I was getting made much sense. It seemed to be representing 6/28/2016 1:24:52.96 PM as 42549.516.
The "vrdate" value I am using is returned by GetValue. For example, in my code I have something like:
value = trend1.GetValue(vrdate, vrstat)
The GetValue command here returns the current value of the trend and the date of when that value appeared.
The float value is the number of seconds since 1st Jan 1970. You can take the difference using the float and convert it to a more meaningful run time in hours, days, or whatever you need to see the time difference represented as. Dividing by 86400 would give you the number of days, the decimal part providing the fraction of a day.
From what I can make out, you are using ProcessBook with some VBA, but unsure how you are trying to use this to calculate the machine run time. A few more details are required to understand what you are trying to do.
I think the issue here stems from the lack of millisecond precision support in both the Variant Date, and Date types in VBA. The vrdate being returned by the GetValue method is a Date being returned as a Variant type. If you try and explicitly cast it back through CDate(vrdate) you will still be missing the subsecond portion of the timestamp. From my nearly rusty memory of how these behave in VBA, the Date should be rounded to the nearest second rather than simply truncating the subsecond component. Further, the DateDiff function doesn't support millisecond level precision anyway.
What order of inaccuracy are you seeing in your DateDiff result?
Thanks for the input everyone.
Here are a few more details to hopefully help better explain what I am trying to do.
I have a trendline that displays the status of a machine (1 indicating running and anything else indicating not running) and I am using the Trend_DataUpdate event in VBA along with GetValue to record when the machine is running and when it is not. When the machine status goes from some value to a 1 (or vice versa), the Trend_DataUpdate event runs and vrdate returns the timestamp of when that change occured. By keeping track of the timestamps, I then use the DateDiff function to calculate how long the machine ran for.
The code ends up looking something like this:
if "machine status was 1 and now isn't 1" then
machine_stat = trend.GetValue(vrdate, vrstat)
runtime = DateDiff("s", last_on, vrdate) 'In this case, "last_on" is the time when the machine started running and "vrdate" is when it stopped
I know that DateDiff only supports accuracy by the second so the Date type in VBA not having milliseconds isn't a big issue. This is where the truncating beomces an problem however.
If I show the data in Processbook by selecting the trendline and going to "Show annotations and details," the timestamps look something like "6/28/2016 9:12:03.9896 AM."
However, this same timestamp in VBA would look like "6/28/2016 9:12:03" as for some reason the vrdate is not rounding to the nearest second.
This ends up causing issues where the machine might actually have ran for 6 seconds but VBA will calculate it as only running for 5 seconds. I am collecting the data over 8 hour periods so these 1 second discrepancies begin to add up and distort the results.