Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |