Count completed dates once from two columns counting only the most recent competion date by week

Lynn Strahm

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!

L_123

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.

L_123

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.

Lynn Strahm

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?

Lynn Strahm

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.