1

So I have a cell with this trick in it to prevent the TODAY() function from updating every day:

IF(C1=0,TODAY(),C1)

Works perfectly for its purpose. However I have other cells with formulas that refer to this cell and it doesn't behave properly. I know for certain it's this cell that's causing the issue but I can't determine why it behaves this way. Furthermore, the exact same formula in different cells gives different results. I've linked a sheet as I feel it's easier to explain this way

https://docs.google.com/spreadsheets/d/1fxeMWPWb1fNRCdyYwjbrjA1_7i9HyfKYzLWFK3fGnCQ/edit#gid=318727776

Basically E2 has a simple formula: = IF(C1=C1,,"world")

E4 is just: = E2 (There's really nothing significant about that. It's just how it's on my original sheet)

And the following is the formula in the cells scattered all over the place: = IF(E4="","hi",0)

There seems to be an imaginary border dividing the sheet where everything on one side behaves one way and everything on the other side another way.

I've found a practical workaround on my actual sheet but this is bizarre to me and I'm trying to understand why it does this ??

Improve this question
2
  • The sheet you shared is not set to public
    – CodeCamper
    Oct 30, 2022 at 4:36
  • I'm sorry. Just set it to public now
    – vkvk
    Oct 30, 2022 at 17:07

2 Answers 2

Reset to default
0

Lambda is the preferred way to freeze today (or any function)

=lambda(fox,fox)(today())

You can test it with now() instead of today() to see it is working.

Remember circular references cause sync issues which you can demonstrate just by opening two tabs and trying to refresh =rand() and with circular references you will have different outputs based on its position in the sheet as calculations are done top left to bottom right something you ordinarily don’t have to worry about unless you use circular refs.

Improve this answer
7
  • "top left to bottom right" I wonder if that's what's in play here?
    – vkvk
    Oct 30, 2022 at 17:09
  • Thank you this works perfectly. "top left to bottom right" I wonder if that's what's in play here? As the imaginary line is roughly a diagonal line that separates top left to bottom right... also can you briefly explain what's going on with the fox,fox? It works perfectly but I want to implement it into other more complicated formulas I have that uses iterative calcs
    – vkvk
    Oct 30, 2022 at 17:15
  • For example this is in cell E11 = IF( AND( ISREF(INDIRECT(TEXT($B$2,"'[yy]mm-dd'!")&"A1")), ISBLANK(E11), IF(ISBLANK(D11),FALSE,NOT(DATEVALUE(D11)=DATEVALUE($B$2))) ), $B$2, IF(E11=0,,E11) ) The first date is in D11 then the next in E11, F11 so forth. The dates aren't necessarily every day. They fill in automatically whenever a new sheet is named today's date that follows this template [yy]mm-dd The today() function is in cell B2. These formulas are populated horizontally across all the 11's I suspect formulas like this and others are slowing down my sheet immensely
    – vkvk
    Oct 30, 2022 at 17:35
  • ugh I'm sorry that's probably unreadable I can't get the formatting right
    – vkvk
    Oct 30, 2022 at 17:36
  • nevermind I got the hang of it. thanks again
    – vkvk
    Oct 30, 2022 at 19:50
0

You cannot get a permanent timestamp with a spreadsheet formula, even with a named function or an Apps Script custom function, because formula results refreshed from time to time. When the formula gets recalculated, the original timestamp is lost.

The easiest way to insert the current date in a cell is to press Control + ; or ⌘;. See the keyboard shortcuts help page.

You can also use an onEdit(e) script to create permanent timestamps. Search this forum for [google-apps-script] timestamp to find many examples.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.