View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default 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.