(over-)due tickets

Jörg Schmidt1

überfällige Tickets.jpg

Hi,

has anyone an idea how to get the number of

• due tickets (due date is yesterday or more)
• overdue tickets (more than 14 / 30 days)

Ray Lindstrom

Hi @Jörg Schmidt1,

I would stick with the square brackets around your column reference instead of curly brackets. Curly brackets are for cross-sheet references. It looks like due to your regional settings you use a semicolon instead of a comma. In that case, try this:

=COUNTIF([benötigt bis]:[benötigt bis]; <=(TODAY() - 1))

It should return the count of rows where the "benötigt bis" is at least 1 day in the past.

Update the -1 to a -14 to see your "over-due" items.

Let me know how it goes!

Ray Lindstrom

Hi @Jörg Schmidt1,

Try this! Of course you'll need to edit the range because our keyboards differ.

Create a couple Sheet Summary fields to capture your metrics.

Due Tickets =COUNTIF(benotigtbis:benotitbis, @cell <= (TODAY() - 1))

Overdue Tickets =COUNTIF(benotigtbis:benotitbis, @cell <= (TODAY() - 14))

All the best,

-Ray

Jörg Schmidt1

Dear Ray,

I am sorry but this does not lead to the due tasks but to all tasks except the one that is due.

in the picture you can see only one is due, the other 5 are not due yet.

A new idea ?? 😀

I tried =COUNTIF({benötigt bis}; <=(TODAY() - 1))

Ray Lindstrom

Hi @Jörg Schmidt1,

I would stick with the square brackets around your column reference instead of curly brackets. Curly brackets are for cross-sheet references. It looks like due to your regional settings you use a semicolon instead of a comma. In that case, try this:

=COUNTIF([benötigt bis]:[benötigt bis]; <=(TODAY() - 1))

It should return the count of rows where the "benötigt bis" is at least 1 day in the past.

Update the -1 to a -14 to see your "over-due" items.

Let me know how it goes!

Ray Lindstrom

The 14 day count formula will include the rows that are included in the 1 day count formula.

Jörg Schmidt1

Hi @Ray Lindstrom ,

I hve to stay with the { } because it is cross-sheet data

If I make -1 I got 4 results

if I make -14 I got 0 as result.

So, this doesn´t seem to be the solution 😏😌

Jörg Schmidt1

@Genevieve P. could you have a look too, please 😁 Thank you

Genevieve P.

Hi @Jörg Schmidt1

I agree with @Ray Lindstrom's formulas! The first one looks for everything that's yesterday or earlier, meaning your overdue items will be included in that count. Do you mean that the first formula should look for rows that are between yesterday and 14 days ago?

If so, try this:

=COUNTIFS({benötigt bis}; <= TODAY(-1), {benötigt bis}; >= TODAY(-14))

One way to see if the formula is finding the correct number of rows would be to put a filter on the sheet and see if it brings up the same count.

If this hasn't helped, it would be useful to see a screen capture of how you use a filter to get the same data. Then we can build a formula to match that filter!

Cheers,

Genevieve

Jörg Schmidt1

Dear @Ray Lindstrom and @Genevieve P. , oviously my fault. Thanks for your help.

Wish you the best. 😁