Skip to main content
Solved

Count days status isn't Complete

  • June 8, 2020
  • 7 replies
  • 45 views
N

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

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?

Best answer by JonathanBowen

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:

  • 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”,

7 replies

JonathanBowen
Forum|alt.badge.img+18

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!
N
  • Nikesh_Ashar
  • Author
  • Participating Frequently
  • June 8, 2020

Good point @JonathanBowen

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

JonathanBowen
Forum|alt.badge.img+18

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:

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

N
  • Nikesh_Ashar
  • Author
  • Participating Frequently
  • June 8, 2020

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.

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • Answer
  • June 8, 2020

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:

  • 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”,

Justin_Barrett
Forum|alt.badge.img+21

@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!

JonathanBowen
N
  • Nikesh_Ashar
  • Author
  • Participating Frequently
  • June 17, 2020

@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

Justin_Barrett
Terms & ConditionsAccessibility statement