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

Options
edited 02/16/21

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.

Tags:

• ✭✭✭✭✭✭
Options

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

• Options

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
• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!