As shown on the left side of the pic below, in cell B7 the function
DAYS(B6, TODAY())
returns a value of 27. This value is wrong because today is October 6 and B6 contains October 30.
If I recompute the exact same formula in another cell, C6 on the right side of the pic, I get the correct value of 24. Cell C7 computes B7-C6 which confirms the difference.
This bizarre behavior in a spreadsheet that has worked fine for a long time, in my opinion can only be an unintended consequence (or at least one not understood by me) of having started to use cell and sheet protection a couple of days ago.
This is a large spreadsheet with a lot of cross-sheet computations and last week I had to spend several hours to find the root cause of some strange results in an unintended accidental modification to one cell. This led me to look into the subject of cell and sheet protection which seemed a rather straightforward way to prevent that issue from happening again.
I, hence, restructured my spreadsheet by collecting all the input values into a single “input sheet” and protected all the other sheets with the exception of a few cells. In the snippet above only cell B2 was unprotected as it is used to select a specific data set.
Before being able to use column C for the testing computations shown in the pic I had to unprotect the sheet.
Does the protection of the sheet somewhat freezes up the value of today() to the protection time?
Even if that was the case though, why would the result of the formula not update after I unprotected the sheet and performed the computations in column C?
This really worries me because, as I said, this is a complex spreadsheet and now I am not sure how many computations I can trust.
For this reason, rather than just fixing this simple issue, I thought wise to ask for help here in case there was a simple solution to make sure the whole spreadsheet gets “fixed”.
I am using Libre Office Version 24.8.1.2 on a Windows 11 Pro laptop.
Please let me know if I omitted some important info.
I appreciate all the support I can receive from the very knowledgeable members of this great community
EDIT: As a newcomer I was allowed a single attachment in my message, but luckily it seems this constraint is easily overcome by doing a later edit. Attached is a simplified version of the spreadsheet in which only the relevant sheets are kept. To eliminate the protection, no password is needed
calculateYield.ods (58.7 KB)