Formula not refreshing when date changes - Can't tell I'm connected?

Kelly M

Hello ~ I'm new to Smartsheet and would appreciate some assistance.  I have a formula that changes the color priority as the next action due approaches or passes, reflecting if the task is pending action from others: Rows that are marked "Done" then gets conditionally formatted to Grey (this was a workaround to not having 5 colors on a RGYB symbol.)

=IF(Status26 = "Completed", "Done", IF(AND(Status26 = "Pending Others", [Due Next]26 <> TODAY()), "Blue", IF(OR([Due Next]26 = TODAY(), [Due Next]26 < TODAY()), "Red", IF(([Due Next]26 - TODAY()) < 3, "Yellow", "Green"))))

Generally, it works great - except occasionally, I seem to be logged out of Smartsheet but I can't tell until I get an error from the Outlook plug in that won't (seemingly) update the Sheet.  The only way I can tell is when the date becomes TODAY(), if something was Blue, it's staying Blue instead of turning Red - the formula is not refreshing the value with the date advance.  If I change the Due Next date to something later and then re-enter it with the original value of today's date (pinging the server), it goes Red correctly.  Resaving the Sheet doesn't trigger it.  It seems like I'm logged in (I just leave the Sheet open all the time), but Outlook won't connect.  If I close and reopen the sheet, multiple duplicate entries will show up on the Sheet and the formulas are all then updated. 

Is there a way to tell if the Sheet is not connecting? to make sure the formula auto-refreshes and I know the values are staying current?  (When the error threw, I reopened the Sheet from scratch, and all was updated.)

Thank you in advance for any feedback!

Alejandra

Hi Kelly,

Since your formula contains the TODAY() function, one of the following must happen in order for the formula to update:

• You open the sheet and save it
• You add a new row to the sheet from a form submission
• You make changes to the sheet from an update or approval request (more on these alerts here)
• You update the sheet by way of a report (more on reports here)
• A linked cell updates the sheet (more on cell linking here)
• You open the sheet being referenced in a cross-sheet formula (more on cross sheet formulas here)

You'll find this information here: https://help.smartsheet.com/function/today

 

Kelly M

Thanks Alejandra, for your response ~ I'll watch for those items going forward. I'm uncertain as yet what the source of the issue is.