Last 7 Days Formula

Stephanie Bauder

I am trying to calculate how many IT tickets have been moved to the status of "completed" within the last 7 days. The formula returns a value of 6 but when I use the filters on the report, it returns the correct value of 2. Can someone help me with the formula so it returns the correct value of 2?

The formula I have is:

=COUNTIFS(Status:Status, "Complete", [Due Date - OVERALL]:[Due Date - OVERALL], >=TODAY(-7))

Paul Newcome

Try adding in some criteria to be less than today.

=COUNTIFS(Status:Status, "Complete", [Due Date - OVERALL]:[Due Date - OVERALL], AND(@cell >= TODAY(-7), @cell <= TODAY()))

Jeff Reisman

Can you share the values from your sheet, and the column types?

Stephanie Bauder

The Status is a drop-down single select column. The Due Date - OVERALL column is a date select column. Using the formula I get 6 tickets that have moved to the status of "complete" in the last 7 days however when I filter the status column to be "complete" and the date column to be "in the last (7) days" I return a value of 2.

Paul Newcome

Try adding in some criteria to be less than today.

=COUNTIFS(Status:Status, "Complete", [Due Date - OVERALL]:[Due Date - OVERALL], AND(@cell >= TODAY(-7), @cell <= TODAY()))

Jeff Reisman

I'm wondering if there are text values in your date select column. Check the box in the column properties to restrict to dates only, and verify all the dates are actually in there as dates by re-selecting them.