Reason why formula driven date changes when sheet is saved

Options

I have a sheet where we use the below formula to calculate the due date for a task. There is a reoccuring issue where when a user saves the sheet these dates are updated, not because the user made a change to the cell itself. It seems to be system driven. It toggles back and forth between a few days. I cannot for the life of me figure out why this is happening but as you can imaging it is causing a major issue as it is a Due Date!

Can anyone tell me if this formula should be updating the due date in this manner when the sheet is saved?

I will attach the activity log and cell history.

In the example it is showing I personally changed 449 cells. This is not the case I actually didn't

make any changes to any cells in this row at all. Activity log attached. This is the same issue across the board ALL day. If a user saves the file then it goes and updates a bunch of cells due dates indicating the user who made the save changed it but they have not changed ANYT

HING in that cell or the cells that are part of the equation.


Answers

  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    Hi,

    Formulas are being recalulated when the sheet is modified. If you don't want formulas to be recalculated, you've to put them in a different sheet, even if the data ist's coming from is the current sheet.

    Let's say data is sitting on sheet A.

    You can create a sheet B with the due date formula pulling data from sheet A

    And finnaly create a formula on sheet A to read the result of the formula on sheet B.


    Doing this and not consulting sheet B (where the due date formula is sitting) won't recalculte the formula.


    Hope this helps.

  • dgloballab
    Options

    It does help but I have to challenge why that is the case? That seems really strange to me that the sheet itself cannot allow for the date to be static.

  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    It's the way formulas are currently working, they are being recalculated if the sheet was modified.

    In your case the formula result contains hour and minutes. So the result of the formula may change every minute, making those big updates.

    Remvoing the hours/minutes from the formula could also be an option.

  • dgloballab
    Options

    how would i go about removing the hour and minutes? this is the formula in that cell:

    Due Date (GLSD Signature) =WORKDAY([First KOS Date]@row, -[SU Duration]@row)

    First KOS Date =MIN([C Clinic KOS]@row, [Y Clinic KOS]@row, [G Clinic KOS]@row, [M Clinic KOS]@row, [H Clinic KOS]@row)

    SU Duration =IF(Complexity1 = "Low", 20, IF(Complexity1 = "Medium", 25, IF(Complexity1 = "High", 35, 99)))

  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    With the given formulas, the result should not change on every save. from what to what values are the changes ?

  • dgloballab
    Options

    They seem to bounce back and forth between 2 dates. Some cases we see it maybe 3 days. It doesn't seem to have a rhyme or reason I can isolate. See the cell history below. I also attached the activity log in the original post which can show you it is happening multiple times a day.

    I dont think this should matter but users do collapse and expand cells while using and saving the file. Again i dont think that should have any baring on why the date is actually CHANGING when none of the inputs for the formula are changing. The column is locked so no one can go in and overwrite formula based dates. So bizzare.


  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    Exapand and collapsing are considered as changes, and they will be recorded aswell.

  • dgloballab
    Options

    Understood it will record but again shouldnt be impacting a formula to change. I can get behind the cell recalculating what I dont agree with is it actually doing different math than what the formula calls for. That is the issue here.

  • dgloballab
    Options

    Do you mind giving me some instruction on how to create file B to do the calculation from file A?

    And then likewise create the formula from File A to pull the date from B into A?