Description: Date in cell A1: 11/26/23 Formula in cell B1: =IF(MONTH(A1)=MONTH(A1+7),B1+7,"") Results shown in B1: 12/30/99 Tested formulas: =month(A1) Result: 11 =month(A1+7) Result: 12 =A1+7 Result: 12/03/23 Steps to Reproduce: Date in cell A1: 11/26/23 Formula in cell B1: =IF(MONTH(A1)=MONTH(A1+7),B1+7,"") Results shown in B1: 12/30/99 Tested formulas: =month(A1) Result: 11 =month(A1+7) Result: 12 =A1+7 Result: 12/03/23 Actual Results: Date in cell A1: 11/26/23 Formula in cell B1: =IF(MONTH(A1)=MONTH(A1+7),B1+7,"") Results shown in B1: 12/30/99 Tested formulas: =month(A1) Result: 11 =month(A1+7) Result: 12 =A1+7 Result: 12/03/23 Expected Results: Month in B1 should be 12/03/23 Reproducible: Always User Profile Reset: No Additional Info: Version: 6.4.7.2 Build ID: 1:6.4.7-0ubuntu0.20.04.8 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded
(In reply to David Fuller from comment #0) > Date in cell A1: 11/26/23 > Formula in cell B1: =IF(MONTH(A1)=MONTH(A1+7),B1+7,"") > Results shown in B1: 12/30/99 That's odd. First, you can't reference B1 in a formula that lives in cell B1, that would be a circular reference Err:522 if the IF condition was TRUE. Second, you are lucky here because the IF condition yields FALSE for the date in A1, thus B1+7 is not executed, but "" is. But for the sake of benefit let's assume you actually have A1+7 instead of B1+7. Third, the "" empty string usually does not yield the null date if formatted, but I don't know how you setup your document or what exactly you are doing. Might it be you used 0 instead of ""? However, a 0 (or "" converted to 0) interpreted as date is the null-date (as dates are expressed as days since null-date) and the null-date in LibreOffice happens to be 1899-12-30, so what you see as 12/30/99 is very likely exactly that, formatted to MM/DD/YY. Fourth, your expectation that the result should be 12/03/23 does not hold, as MONTH(A1) (=11) does not equal MONTH(A1+7) (=12), thus of the IF the A1+7 (B1+7) is not executed. Check your calculations, or write _exactly_ the formulas here you used and how the results are formatted. Or even better, attach a sample document. Closing as not-a-bug. Reopen it if you can provide a reproducible scenario with sample document.
I had mis-typed the actual formula. It is: =IF(MONTH(A1)=MONTH(A1+7),A1+7,"")
The formula is used multiple times through the sheet to give either the next weeks date if the same month or blank it the month has changed. FYI: Using this to print attendance sheets for church.
As requested @Eike, please attach a sample file.