Is there ways to avoid forced Format() rounding?

Question

THIS IS ABOUT FORMAT() FUNCTION AND ITS BEHAVIOR
NOT ABOUT MILLISECONDS OUTPUT!

Examples of time formatting are examples only but not more!

---

We have two sequential values returned by Timer(). After formatting we get rounded time measuring instead of timestamps:

? Format(36411.44921875 / 86400, "hh:nn:ss")
10:06:51
? Format(36410.984375 / 86400, "hh:nn:ss")
10:06:51 ' I'm waiting for 10:06:50 here

Add_1:
I'm not asked how to bypass this issue (like format separatly integer and decimal parts etc.). I'm interesting - is this the native behavior of Format()? Is there specific format string, for example, to avoid rounding?

Add_2:

two sequential values

Sequential is not important here. I just found mentioned behavior on this sequence by eyes in log:

222 16-10-06 10:06:51.449 36411.44921875 0.421428810402199
221 16-10-06 10:06:51.984 36410.984375   0.421423430266204

Sorry for my english...

Add_3:
In my case, I still process the integer and fractional parts separately. So I did so:

Format$(Int(dDate * 86400) / 86400, "hh:nn:ss")

'543 16-10-06 12:03:21.214 43401.21484375 0.502328875506366
'542 16-10-06 12:03:20.667 43400.66796875 0.502322545934606

However, this is just a trick and not for all cases it is convenient...

Add_4:
Apparently, my English is not good enough :(

The problem is that the use of Format() corrupts timestamp!
When taking the timestamp only rounding down is used.

For example, we have now 23:49:59.981765
What is timestamp with hours accuracy? 23. Not 00.
With minutes accuracy? Yes, 23:49, but not 23:50!
With seconds - 23:49:59.
Milliseconds? 23:49:59.981

Not to be confused with timestamps taking and time intervals measurement... In the second case, you can round off as anything... .

Add_4.1:
Playing with 23:49:59.981765 from Add_4 above:

? Format(CDate((TimeValue("23:49:59") * 86400 + .981765) / 86400), "hh")
23 ' *** !!! NOT ROUNDED !!! ***
? Format(CDate((TimeValue("23:49:59") * 86400 + .981765) / 86400), "hh:nn")
23:50
? Format(CDate((TimeValue("23:49:59") * 86400 + .981765) / 86400), "hh:nn:ss")
23:50:00

Conclusion

The Format() behaves as he likes.

Some values it rounds, some - not. By which rules? It is a top secret.
For what reason it uses standard rounding instead of bank rounding in VB(A)? It is a top secret No. 2.

.
++++++++++++++++++++++++++++++++++++++++++++++++
P.S.

First of all - many thanks to Comintern (RotFront!) and Gustav for usefull clarifying of touched problems.

However, my question (statement) - The Format() behaves as he likes - is open still.

It was my big-big mistake when I said the words "Timer()" and "Milliseconds". And even when the title was clarifying all ignored it. I ask in second time - please, forget "Timer()", "Milliseconds" and your catterpillars of digits after dot.

Really there was the only remark from Comintern (RotFront!) on which disclosure I was waited for:

The Date type itself is specified with second precision, and Format in this context works on Date types.

In other words, not Format(), but internal (im- or explicitly) convert to Date datatype is the cause of around. And I was ready to be satisfied with this, but:

A. As say https://msdn.microsoft.com/en-us/library/ms221646.aspx

The variant time resolves to one second. Any milliseconds in the input date are ignored.

We see that milliseconds not only not ignored, but rounded instead of!

B. How_1 they are rounded?
Comintern (RotFront!) is absolutelly right when say:

It is impossible to consider rounding of only a second without looking at it in context of the full time.

In my case the rounding chain breaks on the hours. But it assumed, that all of time components, i.e. seconds, minutes, hours, days, monthes, years are equitable...

C. How_2 they are rounded?
Comintern (RotFront!) wrote:

Windows uses the round half away from zero method1.

Which "Windows"? Who is "Windows"? I know, that VB(A) has a single rounding function - Round() - and it use "Round half to even". All internal rounding at data types conversion use it too. But not "half away from zero".

And only Format(), upstart, white crow, use "half away from zero"... Why?

D. Resolution_1...
And what means "resolution" applying to data type? 500 mcs, 1 sec - there are only a two-three sources where this "resolution" figures sounded... I have the Date==Double data type on the hands. I can't understand - how they can be dropped from continuous numerical line - this 0.5-1 sec...

E. Resolution_2...
Maybe all this misunderstanding is because only internal timers - Date(), Now(), Time(), Timer() - are considered as an only possible sources of timestamps? In my case I get external csv log with reliable authentic timestamps and Format() distorts this excellent data for me.

And even in case of directly Timer() using. I have two counts on display from QueryPerformanceCounter and from GetSystemTimeAsFileTime. For visually evaluate their validity I format both as "hh:nn:ss.000" and place near them Timer() data which distorted by Format()... .

Answer 1

I want to know only - is there ways to control rounding, produced by Format() internaly?

No.

But by custom methods, you can handle milliseconds as you wish.

You can study this article on the topic and download code for all common tasks including methods for display of decimal seconds or milliseconds:

MS Access Can Handle Millisecond Time Values

Please get back if you have questions related to this.

Addendum

I see a lot of confusion here which has side-tracked your question. So, to get back to this, if you wish to get rid of the millisecond part of your values (449 and 984 respectively) to obtain a display of "full" seconds only, you can use this function (taken from the link above) to kill the milliseconds:

Public Function DateTimeRound( _
  ByVal datTime As Date) _
  As Date

' Returns datTime rounded off to the second by
' removing a millisecond portion.

  Call RoundSecondOff(datTime)

  DateTimeRound = datTime

End Function


Private Sub RoundSecondOff( _
  ByRef datDate As Date)

' Rounds off datDate to the second by
' removing a millisecond portion.

  Const clngSecondsPerDay       As Long = 24& * 60& * 60&

  Dim lngDate             As Long
  Dim lngTime             As Long
  Dim dblTime             As Double

  ' Get date part.
  lngDate = Fix(datDate)
  ' Get time part.
  dblTime = datDate - lngDate
  ' Round time part to the second.
  lngTime = Fix(dblTime * clngSecondsPerDay)
  ' Return date part and rounded time part.
  datDate = CDate(lngDate + lngTime / clngSecondsPerDay)

End Sub

Now you can apply Format to the resulting values.

Addendum 2

Corrections to your PS:

The Date type itself is specified with second precision, and Format in this context works on Date types.

In other words, not Format(), but internal (im- or explicitly) convert to Date datatype is the cause of a round.

While it is true that data type is specified down to seconds only, this hasn't blocked for the ability to operate with millisecond precision. Thus, your "other words" should be exactly opposite:

Format(), not an internal (im- or explicitly) convert, is the cause of a round.

Your link to LPSYSTEMTIME adds a lot of confusion as this is about Windows time which has nothing to do with data type Date of VB(A). You should forget all about this as it is internal to Windows and of no use in VBA except for special cases where you can access it via the Windows API (as you will se demonstrated in my millisecond article).

In my case the rounding chain breaks on the hours.

Yes, that will happen if the millisecond part of the value exceeds 500. You can use my function above to avoid that.

I know, that VB(A) has a single rounding function - Round() - and it use "Round half to even". All internal rounding at data types conversion use it too. But not "half away from zero".

You miss many details here. Round and all the Cxxx functions (like CLng) perform Banker's Rounding or "round to even". On the contrary, as the only function of VBA, Format performs a normal 4/5 rounding as you learned in school. Thus, it will consistently round decimal seconds to the nearest integer.

You ask why, and there is no answer to this other than "by design".

If you are really interested on the topic of rounding, note that the native Round is quite buggy. You can read all about this, including documentation and functions that cover all aspects of rounding for any numeric data type of any value here at Github:

VBA.Round

And what means "resolution" applying to data type?

It means the lowest difference between two values. That is exactly 1 ms for the extreme values of data type Date: 100-01-01 00:00:00.000 and 9999-12-31 23:59:59.999, thus is valid for the entire range of Date. Within a small range of months around 1899-12-30 you can go to microseconds and, for a range of a few hours, even to nanoseconds.

I can't understand - how they can be dropped from continuous numerical line - this 0.5-1 sec...

Well, luckily this is not the case, so there is nothing to understand.

In my case I get external csv log with reliable authentic timestamps

That's life, and that is one of the reasons I wrote that article back then on how to handle such cases.

Format() distorts this excellent data for me.

True, because Format was not intended for it. But, again, using my function to cut off the split seconds, you can use Format as is.

As for Timer, I can't see where it comes in for your case. It is really not intended for anything else than its name implies - it returns a Single with a resolution of 1/64 second, and is quite handy for quick timing of a function or a running query. That's all.

Answer 2

tl;dr - There is no rounding rule for seconds. It depends on the entirety of the time.

A Date type is stored internally as a Double, which in turn is an IEEE 754 floating point number with 52 bits of mantissa. The radix has to accommodate values high enough to represent the largest date that is valid for a VT_DATE (December 31st, 9999). Expressed in days since epoch, that comes out to 2,958,465. That means at least 7 digits have to be accommodated to the left of the decimal.

Assuming that the data type accounts for the greatest supported radix, that gives 13 digits without loss of precision to the right of the decimal (see this answer for where that number comes from, and the Wikipedia article on Machine Epsilon for a more detailed explanation). Assuming that the radix is based on the actual number stored (5 digits in your examples), then you get 14 digits of precision. Several methods of rounding are actually defined in the IEEE specification, but Windows uses the round half away from zero method¹.

Keep in mind that this happens a all levels of formatting a Double to a time, because it is defined as fractional days, and each component results in a repeating fraction:

• One second is 1 / 24 / 60 / 60, or .0000115740740740741. Note that VBA rounds this value after 19 digits.
• One minute is 1 / 24 / 60, or .000694444444444444. Note that VBA rounds this after 18 digits.
• One hour is 1 / 24, or .0416666666666667. Note that VBA rounds this also, but at a 16 digit precision.

None of these calculations need to account for a radix, but more importantly they are all comingled with each other. That means any precision loss in any of the hour, minute, or second components can effect all of them. It is impossible to consider rounding of only a second without looking at it in context of the full time.

Now, let's take a look at your code here:

Format$(Int(dDate * 86400) / 86400, "hh:nn:ss")

You mention that you are evaluating the results of the Timer() function, so let's assume for the sake of argument that dDate = Timer(). The first thing to point out is that Timer returns a Single, not a Double. That means the precision of the value returned can only be assumed to be accurate to roughly 7 decimal places (the radix is fixed at 1). In order to coerce this into a Date, you have to scale it and it gets implicitly converted to a Double in the process. But, the entire calculation can't be assumed to be more precise than 7 decimal places. Every additional calculation you perform has the possibility of introducing rounding errors based on precision.

So, the only concrete solution is to not rely on format for precision scales beyond 1 second, and roll your own formatting function or use an alternate storage mechanism (@Gustav points in one direction for this) if you need more precision.

¹ See Inconsistent Rounding of Printed Floating-Point Numbers.