Count Between Two Dates with Other Criteria

Hello, I need to calculate the number of days between two days on another sheet when the name@row matched the person who created the entry and the dates are between my beginning and end dates on my sheet. Here's an example, John enters a ticket with a service date of 3-1-22 and an activity date of 3-4-22. I need to calculate the number of days between those two dates, but only when the service date and activity date fall between my beginning date and end date.

Here's what I came up with, but it's not working; I get unparseable. Anyone out there smarter than me that can lend a hand?

=COUNTIFS({FSE}, Name@row, {Service Request Date}:{Activity Date}, <=[Beginning Date]1, {Service Request Date}:{Activity Date}, >=[Ending Date]1)

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @BFuller

=COUNTIFS({FSE}, Name@row, {Service Request Date}, <=[Beginning Date]1, {Activity Date}, >=[Ending Date]1)

Does this work for you? I'm not sure which date field on your main sheet corresponds to what range on your cross reference sheet.

Kelly

BFuller

Hi Kelly,

Thank you for the response. It worked to count the number of entries, but it did not calcuate the days between the two dates.

Kelly Moore

Hey @BFuller

Sorry, I missed that part. To collect the duration, one has to look at each ticket duration one by one. I am assuming there is the possibility that an individual can have multiple ticket numbers in between any range of dates. If there is the possibility of multiple tickets the formula wouldn't know which start and which stop date to associate with what. Assuming this situation, I find it easiest to first collect this ticket by ticket information on the data entry sheet (your other sheet), then pull summary data, perhaps as an average per person, into this summary sheet. If you want to do that, let me know and I can help you with the helper column on your data entry sheet.

But, to pull an individual ticket that belongs to an individual person into your sheet. I am assuming you have a ticket number list in this summary sheet. If not, we have to set up ways to pull that information in - which is why I calculate the duration on the sheet that already has all the data.

=MAX(COLLECT({Activity Date}, {FSE}, Name@row, {the ticket number column}, [Ticket Number]@row))-MAX(COLLECT({Beginning Date}, {FSE}, Name@row, {the ticket number column}, [Ticket Number]@row))

If you wanted to exclude weekends, holidays, in the duration you would use the appropriate WORKDAY function and place each MAX/COLLECT term in as the range.

I can make this more clear if needed. Please help me better understand your process and I can try to make your solution more specific.

Kelly

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.