SOLVED

Job Status Spreadsheet Display - Filtering for ALL or a restricted set of rows

Copper Contributor

Hello,

I'm using Microsoft 365 Excel, working with a simple worksheet for jobs running through a department. It's a simple log containing a Job Number, Customer, Description, Priority, Date Entered, Date Needed, Date Completed, and Job Owner.

I will be displaying this spreadsheet on a wall monitor for reference for those needing a status of the work. I need a way to display this information in two ways: 1) All rows, and 2) Only those rows where there is no completion date OR when the completion date is 7 days or less from the current date.

Using the Filter option, I can easily get just the blank rows to display, but have not been able to get the rows to display where DAYS=TODAY()-Date Completed=<7, and I need both.

I also can't figure out an easy way to toggle between displaying "all" and only the incomplete or recent completions.

For extra points, when I complete a job with a priority of 1, I would like to auto-update #2 to #1, 3 to 2, etc.

Any and all help is greatly appreciated. I seem to get this screen display so close, but so far I can't get both conditions to work correctly....Blank completion date AND completion date within 7 days from today.

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@TechCenter1000 Use this formula in the Days column

=OR(ISBLANK(I2),(TODAY()-I2)<=7)

 and filter out FALSE, showing only TRUE.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@TechCenter1000 Use this formula in the Days column

=OR(ISBLANK(I2),(TODAY()-I2)<=7)

 and filter out FALSE, showing only TRUE.

View solution in original post