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

Find more posts tagged with

Accepted answers

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!

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.