I’ve set format of 2 cells (say A1 and A2) to HH:MM:SS.MS. Now if I set third cell to get difference - =A2-A1 - it shows wrong value text: #VALUE!. How to fix it? Thanks in advance
-
HH:MM:SS.MSisn’t a reasonable time-format:string. It would (wrongly?) be accepted by Calc. but the partMSbehind the point would a second time return the numbers of minutes and of seconds - both reduced to a single digit where “possible” - which doesn’t make any sense. - If you actually wanted to get the time resolved down to milliseconds (ms), you should have used the format-string
HH:MM:SS.000. - Time-only formats often are applied in a gravely misleading way by naive users: The actual value contained in or calculated for the cell may be a complete date-time-value, but the date part can be suppressed for the display by the formatting. If the cell later is referenced in a formula, the full value, including the integer part for the date, is returned. E.g.: Two assumed TOD cells showing exactly the same time can then result in a difference of any integer value.
- Accepting a tiny amount of exaggeration I might say “Number formats are made for lying.” For topicality: That’s similar to how history is “used” in political quarrel.
- To abandon the lying effects the only definitely safe way is to
communicate / show / keep as datadates, TOD values, date-time values, and also (differently formatted) durations as specified by ISO standards.
2 Likes
Number formats and cell values are independent. No formatting attribute changes any cell value. The text “12:34:56.789” is not the same value as the formatted number 12:34:56.789. Number formats do not apply to text.
t74377.ods (32.4 KB)
1 Like
I think one or both your cells are text, not dates. Please see How to convert number text to numeric data .
Cheers, Al