(over)due tickets
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)
Best Answer

Hi @Jörg Schmidt1,
I would stick with the square brackets around your column reference instead of curly brackets. Curly brackets are for crosssheet 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 "overdue" items.
Let me know how it goes!
Answers

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

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))

Hi @Jörg Schmidt1,
I would stick with the square brackets around your column reference instead of curly brackets. Curly brackets are for crosssheet 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 "overdue" items.
Let me know how it goes!

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

Hi @Ray Lindstrom ,
I hve to stay with the { } because it is crosssheet 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 😏😌

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

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Dear @Ray Lindstrom and @Genevieve P. , oviously my fault. Thanks for your help.
Wish you the best. 😁
Help Article Resources
Categories
Check out the Formula Handbook template!