https://bugs.documentfoundation.org/show_bug.cgi?id=172582
Bug ID: 172582
Summary: Date time representation has a 23:23:59 error due to
rounding, with floats close to midnight.
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
When a cell contains a datetime serial number whose value is a tiny fraction
below midnight (e.g. `46132.9999999999`), LibreOffice Calc displays the
**date** component correctly (rounds up to the next day) while the **time**
component is wrong (shows `23:59:59` instead of `00:00:00`). The same value in
Microsoft Excel displays both components consistently as `00:00:00`.
Steps to Reproduce:
1. Open LibreOffice Calc with a new, empty spreadsheet.
2. Click on cell **A1**.
3. Type the following value exactly and press Enter:
46132.9999999999
4. With A1 still selected, open **Format > Cells > Numbers** and apply the
format:
DD/MM/YYYY HH:MM:SS
5. Read the displayed value in A1.
Actual Results:
21/04/2026 23:59:59
The date component is correct (21 April 2026) but the time component is wrong
(23:59:59 instead of 00:00:00). The apparent gap between the two displayed
values is 86 399 seconds, exactly 24 hours minus one second.
Expected Results:
21/04/2026 00:00:00
The value `46132.9999999999` is less than one millisecond below midnight at the
start of 21 April 2026. It should round to midnight and display as `00:00:00`.
This is the behaviour of Microsoft Excel and of simple round-before-split
logic.
Reproducible: Always
User Profile Reset: No
Additional Info:
LibreOffice uses **two separate code paths** to extract the date and time
components of a serial number, and they apply different rounding strategies.
1) Date path : i18npool/source/calendar/calendar_gregorian.cxx,
setLocalDateTime()
double fM = fTimeInDays * U_MILLIS_PER_DAY; // multiply by 86 400 000
double fR = rtl::math::round( fM ); // round to nearest millisecond
For `46132.9999999999`, multiplying by 86 400 000 gives `3 985 891 199
999.914`, which rounds up to `3 985 891 200 000` ms = exactly `46133.0` > **21
April 2026** (correct).
2) Time path : tools/source/datetime/ttime.cxx, GetClock()
const double fTime = fTimeInDays - rtl::math::approxFloor(fTimeInDays);
approxFloor is defined in include/rtl/math.hxx:
inline double approxFloor(double a)
{
return floor( approxValue( a ));
}
which delegates to `rtl_math_approxValue` in `sal/rtl/math.cxx`:
double SAL_CALL rtl_math_approxValue(double fValue) noexcept
{
const double fBigInt = 0x1p41; // 2^41 -> only 11 bits left for fractional
part
if (fValue == 0.0 || !std::isfinite(fValue) || fValue > fBigInt)
return fValue;
double fOrigValue = fValue;
bool bSign = std::signbit(fValue);
if (bSign)
fValue = -fValue;
if (isRepresentableInteger(fValue) || getBitsInFracPart(fValue) <= 11)
return fOrigValue;
int nExp = static_cast<int>(floor(log10(fValue)));
nExp = 14 - nExp; // target 14 significant digits
double fExpValue = getN10Exp(abs(nExp));
if (nExp < 0) fValue /= fExpValue;
else fValue *= fExpValue;
if (!std::isfinite(fValue)) return fOrigValue;
fValue = std::round(fValue); // round to 14 sig. digits
if (nExp < 0) fValue *= fExpValue;
else fValue /= fExpValue;
if (!std::isfinite(fValue)) return fOrigValue;
return bSign ? -fValue : fValue;
}
The key line is nExp = 14 - nExp: it scales the value so that `std::round`
lands at exactly 14 significant decimal digits. For `46132.9999999999`, which
already has 14 significant digits, the value passes through essentially
unchanged. `floor()` then sees `46132.9999999999` and returns `46132` — one day
short of midnight:
approxValue(46132.9999999999) = 46132.9999999999 ← 14 sig. digits, still <
46133
floor(46132.9999999999) = 46132
fTime = 46132.9999999999 − 46132 = 0.9999999999
0.9999999999 × 86400 = 86399.999... seconds → truncated to 86399 = 23:59:59
(wrong)
The function then deliberately **truncates** (not rounds) the seconds to avoid
rolling over to `24:00:00`. Its own source comment reads:
> *"do not round values (specifically not up), but truncate … so 23:59:59.99 is
> still
> 23:59:59 and not 24:00:00 (or even 00:00:00 which Excel does)"*
(Explanation generated by an AI. By making the AI prove the hypothesis of why
the bug occurs, the downloaded source code was analyzed).
--
You are receiving this mail because:
You are the assignee for the bug.
