Greetings Using Libre Office 7.1.7.2, Build c6a4e3954236145e2acb0b65f68614365aeee33f on a German Mac OX X 10.15.7, Catalina, powerbook, set in the OS control panel to use the english language for this app. When formatting a cell with a custom time format containg a "d" for days the shown value is one off (too high). E. g. use a cell containing numerical 12.5 (i.e. twelve and a half days) the format code [d " d " h " h "] should return '12 d 12 h'; it returns '13 d 12 h'. Happens also, when the app is set to native German format on the machine. Very annoying when you try to display time intervals and two cells with identical dates (subtraction resulting in "0") are allegedly one day apart. Took me a while to notice.... BTW: a numerical -1.5 is displayed as '30 d 12 h' - so there. Cheers J W
(In reply to Joachim Wetzig from comment #0) > When formatting a cell with a custom time format containg a "d" for days the > shown value is one off (too high). No, it isn't correct. > E. g. use a cell containing numerical 12.5 (i.e. twelve and a half days) the > format code [d " d " h " h "] should return '12 d 12 h'; it returns '13 d 12 > h'. No, it should (and does) return '11 d 12 h', because 'd' is not a *duration* format, but a calendar date's day. Datetime's value '0' is the epoch, which *by default* (see Options->Calc->Calculate) means 1899-12-30; '1' means 1899-12-31; '2' means 1900-01-01; and so on - so '12' means 1900-01-11, of which, the '11' is used for the 'd' code. If you want durations in *days*, just use something like =INT(A1) & " d " & TEXT(A1; "h "" h """)
Format code D is *day of month* of a serial date number, not duration in days.. If 12.5 is formatted as date+time then it is null-date 1899-12-30 plus 12.5 days => 1900-01-11 12:00:00 For that, format code D would display 11 though; if it displays 13 for you then you have set the null-date to either "1900-01-01 (StarCalc 1.0)" (where resulting date+time would be 1900-01-13 12:00:00) (unlikely) or "1904-01-01" (where resulting date+time would be 1904-01-13 12:00:00). The latter is likely the case if you loaded an Excel document created on Mac, as 1904-01-01 was the null-date for MS-Excel on Mac, not 1899-12-31 as for Excel on Windows Btw, -1.5 then is 1903-12-30 12:00:00 - so there.. Btw2, LibreOffice's standard null-date is 1899-12-30 instead of 1899-12-31 because Excel kept its bug to regard 1900 as a leap year pretending there was 1900-02-29; LO starting 1 day earlier at least uses the same serial date number for all dates on or after 1900-03-01.
Thanx for clearing up my confusion. (I was led astray by A**les Numbers, which uses a duration code) May I suggest to state the ‘calendar date’ character of the “d” code -and the dependence on the zero time setting- more clearly in the help files (where I seatched before committing the report). Again, thanks for un-confusing me, and somspeedy, too :=))))