Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Count days status isn't Complete

Topic Labels: Formulas
Solved
Jump to Solution
922 7
cancel
Showing results for 
Search instead for 
Did you mean: 

What would be the correct way to have the duration / age of a ticket in Airtable being any status that isn’t “Complete”?

airtable_days_open

In the “Days Open” field I currently have got to this formula :

IF(LAST_MODIFIED_TIME(Status), WORKDAY_DIFF( TODAY(), LAST_MODIFIED_TIME({Status}) ) )

What’s the best way to stop it counting once the ticket is completed?

1 Solution

Accepted Solutions

OK, so there’s a couple of things there that don’t look right:

  • You can set up a “created time” field for each record. I would do it like this to just get the date value:

Screenshot 2020-06-08 at 16.04.00

  • You can then set up a WORKDAY_DIFF formula. Configure this so that the earliest date (created date) is first. Something like:

WORKDAY_DIFF({Created Date}, TODAY())

You should find this will give you positive numbers for the number of days.

  • You don’t need to worry about the existence of the created date field as this will always be populated (if set up as described here). So now your formula can be:

IF({Status} != 'Complete', WORKDAY_DIFF({Created Date}, TODAY()))

You can’t do this, I’m afraid:

LAST_MODIFIED_TIME(Status) != “Complete”,

See Solution in Thread

7 Replies 7

Hi @Nikesh_Ashar - I’m not sure what your process is, but I think you might want to make the days open calculation based on “created date” rather than a last modified time. I’m guessing that the status might get updated multiple times and as it stands your formula would work off the last update to the current status (which might not be the “origin” status).

But…to answer your specific question you can do something like this:

IF(AND({Status} != 'Complete', YOUR_DATE_FIELD), WORKDAY_DIFF(...))

So this is saying:

If the status is not ‘Complete’ AND you have a date field, then do the workday diff calc


If this answers your question, please consider marking this as the solution. If not, please post again for more help. Thanks!

Good point @JonathanBowen

I’ll update the formula to include created date. Will it automatically ignore the time?

You can set it so that time is not included in the value:

Screenshot 2020-06-08 at 15.24.46

But, yes, WORKDAY_DIFF looks like it ignores any time value.

I’ve now got to this formula :

IF(LAST_MODIFIED_TIME(Status) != “Complete”, WORKDAY_DIFF( TODAY(), CREATED_TIME({Date created})) )

It doesn’t seem to have changed anything and still giving me negative values in the cells.

OK, so there’s a couple of things there that don’t look right:

  • You can set up a “created time” field for each record. I would do it like this to just get the date value:

Screenshot 2020-06-08 at 16.04.00

  • You can then set up a WORKDAY_DIFF formula. Configure this so that the earliest date (created date) is first. Something like:

WORKDAY_DIFF({Created Date}, TODAY())

You should find this will give you positive numbers for the number of days.

  • You don’t need to worry about the existence of the created date field as this will always be populated (if set up as described here). So now your formula can be:

IF({Status} != 'Complete', WORKDAY_DIFF({Created Date}, TODAY()))

You can’t do this, I’m afraid:

LAST_MODIFIED_TIME(Status) != “Complete”,

@Nikesh_Ashar Were the suggestions from @JonathanBowen able to solve your problem? If so, please mark his latest comment as the solution. If you still need assistance, let us know!

Done - thank you. Still getting used to this platform