Description: Using a formula of [=TEXT((A29)/(24*60*60),"d\d h\h m\m s\s")] when A29 is 86400 (i.e., 24*60*60) the formula returns an answer of 31d 0h 0m 0s. When A29 is 390193.54 the formula returns 3d 12h 23m 13s but the correct answer should be 4d 12h 23m 13s. The day portion of the calculation is not calculating correctly. Steps to Reproduce: 1. Use formula =TEXT((A29)/(24*60*60),"d\d h\h m\m s\s") 2. In A29, enter 86400. 3. Actual Results: 31d 0h 0m 0s Expected Results: 0d 0h 0m 0s Reproducible: Always User Profile Reset: No Additional Info: Excel does not have this problem, but computes the answer correctly.
Please attach a sample file, reduce the size as much as possible without private information, And paste the information in Menu/Help/About LibreOffice, there is a copy icon.
Format code keyword D is not amount of days but day of month, a calendar day. If you have the value 86400 in A29 and divide it by (24*60*60) the result is 1. For the D format keyword the value 1 is taken as an offset to the null-date 1899-12-30 yielding 1899-12-31 hence the display value 31d. Excel only *appears* to calculate correctly because it uses the null-date 1899-12-31, adding 1 to that yields 1900-01-01 hence 1d if formatted as day of month. Try your formula with any value > 86400*31 ... Calc uses null-date 1899-12-30 instead of 1899-12-31 because Excel has a bug in that it thinks 1900 was a leap year and counts 1900-02-29 as valid date..