(over-)due tickets

Jörg Schmidt1
Jörg Schmidt1 ✭✭✭✭
edited 12/13/22 in Formulas and Functions

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

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    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

  • Ray Lindstrom
    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
    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
    Ray Lindstrom ✭✭✭✭✭✭
    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!

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

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

  • Jörg Schmidt1
    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
    Jörg Schmidt1 ✭✭✭✭

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

  • 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Jörg Schmidt1
    Jörg Schmidt1 ✭✭✭✭

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

    Wish you the best. 😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!