(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 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!
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 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!
-
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 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 😏😌
-
@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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Dear @Ray Lindstrom and @Genevieve P. , oviously my fault. Thanks for your help.
Wish you the best. 😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!