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

[Chandler_Quinti]

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!

[Chandler_Quinti]

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!

[Chandler_Quinti]

I hope someone sees this :slightly_smiling_face:

[Jeremy_Oglesby]

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

[Chandler_Quinti]

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.

Airtable

Bug Tracker Template - Free to Use | Airtable

Itching for a better debugging process? This bug tracking system is ideal for QA analysts, test engineers, or any fast-moving team that needs a collaborative...

(Bugs and Issues Table, “Days Old” Column)

[Jeremy_Oglesby]

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

[df]

I'm getting the same NaN error.

Here's the formula I'm using. 

[df]

Actually, I think i figured my issue out. I removed the 'MM-DD-YYY' portion in my formula.