Hello, and first off, thank you to everyone for all the help in the community. Long time lurker, first time poster!
My business uses a series of documents that go through a few statuses - drafted - sent - viewed - and complete.
The way my table is set up and with a few little automations I have the ability to know the duration between those statuses. The problem is, I only can get the duration between the statuses when they change, not the duration that’s been going on between that time - if that makes sense.
My end goal is to know (even roughly) how much time (days are fine) since a last update of any of the actions. For example, I would like to know how long it’s been since ‘viewed’ so I can see if a document is past the parameters of being on-time and contact my customer to support them on completing their document.
More simply - I would like to know how long something is ‘in’ one of the statuses - again, rough dates are totally ok.
Initially I tried using a today() formula, but nothing was working quite right. To provide further context, the way I am capturing the time between the two dates is that I have multiple ‘last modified’ fields that are only updated when a status is changed. When my Zapier automation marks the status change, I also mark ‘true’ a checkbox that is connected to the last update to get that time. When the next in line status change occurs, I can easily calculate the time as each checkbox has given me the time that status was activated. Unfortunately, that means I’m only aware of a document being late after it goes to the next step.
Any and all creative ideas are appreciated - thank you in advance!