Date formula Question

Hello,

I am looking to count how many item are late +5 days. I am referencing another sheet where I collect information on item type, date received, and date tested. I want to know how many of that item has been waiting to be tested (+5days) since received.

Sample formula: =COUNTIFS({Sheet1-ItemCategory}, "Blue", {Sheet1-Date Received}, {Sheet1-Date Tested} < (+5))

Best Answer

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓

    Is it possible to add a "helper" column to the sheet that has the "Date Received" and "Date Tested" columns?

    You could set it up as a Checkbox column (TestingOverdue), and insert the following:

    =IF(OR(AND(DateReceived@row < TODAY(-5), ISBLANK(DateTested@row)), DateTested@row > (DateReceived@row + 5)), 1, 0)

    Then, on your data aggregation worksheet, you can insert a simple formula to count the flags (=COUNTIF({Sheet1-TestingOverdue}, 1)

    Hope this is helpful!

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 05/24/23

    The below formula will look at the date received plus 5 days and if the Sheet date tested is greater than that date for a Blue item category it will count it.

    =COUNTIFS({Sheet1-ItemCategory}, "Blue", {Sheet1-Date Tested},>({Sheet1-Date Received}+5))

    If you want just at the 5th day you would use

    =COUNTIFS({Sheet1-ItemCategory}, "Blue", {Sheet1-Date Tested},=({Sheet1-Date Received}+5))

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓

    Is it possible to add a "helper" column to the sheet that has the "Date Received" and "Date Tested" columns?

    You could set it up as a Checkbox column (TestingOverdue), and insert the following:

    =IF(OR(AND(DateReceived@row < TODAY(-5), ISBLANK(DateTested@row)), DateTested@row > (DateReceived@row + 5)), 1, 0)

    Then, on your data aggregation worksheet, you can insert a simple formula to count the flags (=COUNTIF({Sheet1-TestingOverdue}, 1)

    Hope this is helpful!

  • Hello Hollie! both of those formulas are coming back as "#UNPARSEABLE" and Danielle I would rather keep the original sheet as user friendly as possible but ended up using your formulas (hid the columns) and they work! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!