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. |
All times are GMT +1. The time now is 01:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com