How would you calculate time or duration from a 'last modified' field to the current date?

Topic Labels: Formulas
1827 5
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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!

5 Replies 5
4 - Data Explorer
4 - Data Explorer

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 quadrupleScreen Shot 2021-01-28 at 12.01.55 PM Screen Shot 2021-01-28 at 12.02.01 PM Screen Shot 2021-01-28 at 12.02.06 PM checked the formatting and settings and they’re 1:1.

Thought this might help!

I hope someone sees this :slightly_smiling_face:

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”.

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)

Can you show us screenshots of your implementation that is not working?