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
-
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
-
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))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 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!