COUNTIFS Resolved (or not) within 2 days over the last 7 days

John Hageman

I have the following columns:

Date (I refer to this as 'Date Opened' below)

Resolved (checkbox)

Resolved Date

Formulas in the 'Sheet Summary' section

I am trying to create these formulas to reflect on the last 7 days, will be used to make a dashboard chart widget:

1. Resolved on time (2 Days) over the last 7 days
   1. Count the number of times the Resolved checkbox is checked and has a Resolved Date within 2 Days of the 'Date Opened' field. Count all occurrences over the last 7 days.
   2. This is my attempt at the formula, but it just returns to 0 when it should meet criteria.
      1. =COUNTIFS(Resolved:Resolved, 1, [Resolved Date]:[Resolved Date], @cell <= TODAY(), [Resolved Date]:[Resolved Date], @cell >= TODAY(-2), Date:Date, @cell <= TODAY(), Date:Date, @cell >= TODAY(-7))
2. In Progress
   1. Count the number of times the Resolved checkbox is unchecked and Today's date is within 2 Days of the 'Date Opened' field.
3. Not Resolved on time (2 Days) over the last 7 days
   1. Count the number of times the Resolved checkbox is checked or not checked and has a Resolved Date past 2 Days of the 'Date Opened' field. Count all occurrences over the last 7 days.

How would this be achieved if I am only interested in workdays (M-F)?

For these formulas, I will be able to see if the service level agreement has been met for the year:

1. Resolved on time (2 Days) over the last 7 days, Running Total.
   1. Count the number of times the Resolved checkbox is checked and has a Resolved Date within 2 Days of the 'Date Opened' field. Count all occurrences.
2. Not Resolved on time (2 Days) over the last 7 days, Running Total.
   1. Count the number of times the Resolved checkbox is checked or not checked and has a Resolved Date past 2 Days of the 'Date Opened' field. Count all occurrences.

Heather Duff

Hi John,

The best way to achieve this may be to add another column. I called that column Duration. Here's the formula I entered in that column:

=IF(Resolved@row = 1, NETWORKDAYS(Date@row, [Resolved Date]@row), NETWORKDAYS(Date@row, TODAY()))

This translates to:

If Resolved is checked, count the number of work days between the start date and the resolved date. Otherwise, count the number of work days from the start date to today.

On my sheet summary, I was able to use these formulas:

Resolved on time (2 days) past 7 days:

=COUNTIFS(Resolved:Resolved, 1, Duration:Duration, <3, Date:Date, >=TODAY(-7))

In Progress: **Note: this only counts items that are NOT overdue.

=COUNTIFS(Resolved:Resolved, 0, Date:Date, <=TODAY(-2))

Not resolved on time (2 days) past 7 days:

=COUNTIFS(Duration:Duration, >2, Date:Date, >=TODAY(-7))

Resolved on time (running total):

=COUNTIFS(Resolved:Resolved, 1, Duration:Duration, <3)

Not resolved on time (running total):

=COUNTIF(Duration:Duration, >2)

Hope this helps!

Best,

Heather

John Hageman

Hi Heather,

Thanks to your formula, I was able to utilize my existing "Days to Resolution Column".

Here are the results of the formulas in the sheet summary:

1. Resolved on time (2 days) past 7 days: #INVALID DATA TYPE
2. In Progress: This seems to be only counting the instances that the resolved Checkbox and not accounting for the 2 day period. I changed less than symbol to a greater than symbol, resulting the the formula: =COUNTIFS(Resolved:Resolved, 0, Date:Date, and this appears to be working.
3. Not resolved on time (2 days) past 7 days: #INVALID DATA TYPE
4. Resolved on time (running total): #INVALID DATA TYPE
5. Not resolved on time (running total): #INVALID DATA TYPE

John Hageman

Reviewing the formula:

=IF(Resolved@row = 1, NETWORKDAYS(Date@row, [Resolved Date]@row), NETWORKDAYS(Date@row, TODAY()))

If the Resolved Date is the same day as the Date Opened, the result will be 1. Instead, I would like this to be 0, since a whole day has not occurred. How would I make this change? Thank you

John Hageman

I figured out why I was receiving the #INVALID DATE TYPE error. It was because I have blank Resolved Date fields. I got it working now that I entered this data.

I just need to know how to change the following formula to meet the desired outcome below:

=IF(Resolved@row = 1, NETWORKDAYS(Date@row, [Resolved Date]@row), NETWORKDAYS(Date@row, TODAY()))

If the Resolved Date is the same day as the Date Opened, the result will be 1. Instead, I would like this to be 0, since a whole day has not occurred. How would I make this change?

Thank you

markkrebs

You have subtract a day. The following example is similar. Also, for the invalids you may be able to use the ISDate function so it ignores non dates. Hope this helps

=IF(ISDATE([Baseline Completion Rollup]@row), IF([Baseline Completion]@row < [End Date]@row, -1 * NETWORKDAY([Baseline Completion Rollup]@row, [End Date]@row, {Holidays}) + 1, IF([Baseline Completion]@row = [End Date]@row, 0, NETWORKDAY([End Date]@row, [Baseline Completion Rollup]@row, {Holidays}) - 1)))