Reason why formula driven date changes when sheet is saved

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

Activity Log_15Nov.xlsx

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

Find more posts tagged with

Accepted answers

All comments

Etienne Mermillod

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

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

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

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

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

dgloballab

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

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

dgloballab

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

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.