Count completed dates once from two columns counting only the most recent competion date by week
I'm trying to count the number of quotes completed by week. The challenge is there are two departments who may work on a quote and each enter their own completion date. Sometimes only one department works on a quote so only one of the two completion date columns is filled in. In a nutshell I want to only count "Type A" quotes that were completed within a specific weekending date range (ex. week that ended on 8/8/20) based upon the most recent completion date of department "A" or "B" for the quote. Keep in mind that either department "A" or "B's" completion date field may be blank if they didn't need to work on it.
I figured out how to count "Type A" quotes based on one department's completion date but counting both date ranges and selecting the most recent has me stumped. Below is the formula I've came up with so far:
=COUNTIFS({Pre-Sales Request Range 1}, ="NO DWG, EWR ONLY", {Pre-Sales Request Range 2}, AND(@cell >= ([Week Ending Date]21 - 6), @cell < ([Week Ending Date]21 + 1)))
Any help would be greatly appreciated, Thanks!
Best Answer
-
this is probably easier to do with the weeknumber formula than date parsing
=count(collect(created:created,datea:datea,weeknumber(@cell) = weeknumber(max(max(datea:date),max(dateb:dateb))),dateb:dateb,weeknumber(@cell) = weeknumber(max(max(datea:date),max(dateb:dateb)))
so hopefully that can help get you started. The formula above will find the maximum date in either columns datea and dateb, return the weeknumber, and count all returns. If both of them have a submission on said week it will only count it once.
Answers
-
this is probably easier to do with the weeknumber formula than date parsing
=count(collect(created:created,datea:datea,weeknumber(@cell) = weeknumber(max(max(datea:date),max(dateb:dateb))),dateb:dateb,weeknumber(@cell) = weeknumber(max(max(datea:date),max(dateb:dateb)))
so hopefully that can help get you started. The formula above will find the maximum date in either columns datea and dateb, return the weeknumber, and count all returns. If both of them have a submission on said week it will only count it once.
-
Thanks for the prompt response! I'm confused on what range the "created:created" should tie back to in the formula provided. Is this the week number that we want to count completed dates for?
-
Some additional information, here is the formula I'm currently using: =COUNT(COLLECT(WeekNumber21, {Pre-Sales Request Range 2}, WEEKNUMBER(@cell) = WEEKNUMBER(MAX(MAX({Pre-Sales Request Range 2}), MAX({Pre-Sales Request Range 3}))), {Pre-Sales Request Range 3}, WEEKNUMBER(@cell) = WEEKNUMBER(MAX(MAX({Pre-Sales Request Range 2}), MAX({Pre-Sales Request Range 3}))))). It doesn't seem to matter what range or cell I use for the COLLECT function rage I get a "1" returned for this formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!