Created attachment 114186 [details] Bug example (in sheet Calcul, cells H14 and I14) Hello. I am doing operations with the functions DAYS(), INDIRECT() and ADDRESS() and have abnormal results. I finally found that the problem comes from the value returned by the function DAYS(), which are not integers. In the attached example, go to sheet 'Calcul' and cell H14 (coloured in dark pink). You may see that the cell contains the formula "=DAYS(D14;C14)" which returns as value "364,99999724537300000000000000". Thus when I lated use this value to find a cell reference as done in the cell I14 (formula : "=ADDRESS($H14;B$22)"), the result is wrong ($H$364 instead of $H$365). So far, I am working around this by replacing "=DAYS(D14;C14)" by "=round(DAYS(D14;C14))".
Hello, when I change of cell format of H14, then I get value 365, but calculation is still bad. DAYS work correct with dates 01/01/12 , 31/12/12. Problem is probably in chain of cells> =DAYS(D14,C14) - problematic C14 lead to B4 lead to B73 - function if and index lead to sheet MeteoFrance.G22 lead to B22 lead to B23 the problem is in one second in your date, see attachment. Closing as notabug, repair your data.
Created attachment 114190 [details] simple case
ODF 1.2 specifications. 6.10.6 DAYS Summary: Returns the number of days between two dates Syntax: DAYS( DateParam EndDate ; DateParam StartDate ) Returns: Number Constraints: None Semantics: Returns the number of days between two dates. If StartDate and EndDate are Numbers, this is EndDate – StartDate. If they are both Text, this is DATEVALUE(StartDate) – DATEVALUE(EndDate). Can return not only whole days, but part of a day. All values in column B of MeteoFrance sheet have decimals, I think the source of the issue.
Hello. Thanks for your help. I have worked around this by applying ROUND to the dates themselves, thus getting rid of any hours:minutes:seconds offsets with the dates. That ends up being something like : DAYS ( ROUND (Date 1); ROUND (Date 2)). Consequently, I believe the bug can be closed as it is not one but a mistake on my side (and maybe a need for more explanation in the function wizard if I may suggest). Thanks again.