Date formula Question

munoz_rita

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

Danielle Arteaga

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!

Hollie Green

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

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!

munoz_rita

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!