I have an interesting situation that i'm trying to write a formula for. The spreadsheet I've attached is a sample of a more expansive production. I compile this expansive report 3 times a week and send it out to the folks who have assignments to show each item they're assigned and if the deadline is coming due (based on a R/Y/G grading system). The report provides a summary sheet, as well as sheets with the detailed items based on which of 3 statuses their assignments are in - Review, Investigate, or Action Pending.
My problem lies when compiling the formulas for the summary sheet, trying to summarize the Review status. I've got Investigate and Action Pending solved, but the Review is a bit more tricky.
Items in Review should be reviewed and moved to one of the other statuses within 2 days of the assigned date (technically, 2 business days, but that's less important for my question). I can throw in a Countif for all the items in the Assigned Date column (column E) that are less than TODAY and it works fine, however, I need to count the dates in column E +2 days and compare against Today.
TLDR:
I need to add 2 days to all the dates in column E of the Review sheet and count how many of those dates are less than TODAY (and place that count on C2 of the Summary sheet)
essentially, something along the lines of (in C2 of the Summary sheet) =COUNTIF(Review!E:E+2,"<"&TODAY()), even though I know that formula won't work
I was able to get the conditional formatting to work this out to apply R/Y/G as needed, and I'm fully aware I could just throw in an additional column to add the 2 days, and base my count off of the additional column, but I'm trying to keep everything clean for the end user. So, if there's a way to add 2 days to a date range within a counting formula, I'd love to find it.
Thanks, as always!
Bookmarks