Jan 28, 2021 08:39 AM
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!
Jan 28, 2021 09:02 AM
In one of the bug tracker templates, this function seemingly exists:
There’s a formula column with CREATED_TIME()
and a column next to it with DATETIME_DIFF(TODAY(), DATETIME_PARSE({Opened date}, ‘MM-DD-YY’), ‘days’) and it works.
Now, when I try to copy this EXACTLY into my own table, it comes back NaN. I have quadruple checked the formatting and settings and they’re 1:1.
Thought this might help!
Feb 11, 2021 09:39 AM
I hope someone sees this :slightly_smiling_face:
Feb 11, 2021 09:43 AM
I’m confused by the screenshots. It looks to me like it’s working - I don’t see NaN
, I see values of 45
, 43
, and 0
for “Days old”.
Feb 11, 2021 12:55 PM
Correct - it works (as an example) in the bug tracker template. When I copy the methodology exactly, it does not work. Only in the template for some reason.
(Bugs and Issues Table, “Days Old” Column)
Feb 11, 2021 04:13 PM
Can you show us screenshots of your implementation that is not working?
Oct 24, 2024 11:28 AM
I'm getting the same NaN error.
Here's the formula I'm using.
Oct 24, 2024 11:45 AM
Actually, I think i figured my issue out. I removed the 'MM-DD-YYY' portion in my formula.