Ouch! Inconsistent TEXT("hh:mm") rounding
I have recommended using --TEXT(...,"hh:mm") to ensure that the resulting
time value is identical to the binary form of the displayed time value. But
note....
If the time specification is 12:33:59, TEXT(...,"hh:mm") truncates to 12:33.
But if the time specification is 12:33:59.5, TEXT(...,"hh:mm") rounds to
12:34.
My interpretation: Excel first rounds to the second, then it truncates to
the minute.
In fact, TEXT(...,"hh:mm:ss") does indeed consistently round to the second.
In contrast, TEXT(...,"h") consistently truncates to the hour (after
rounding to the second).
So we cannot count on TEXT(...,"hh:mm") to round or truncate to the minute.
We can only count on it to give us the same thing as it would display.
Fortunately, that is usually what we want.
|