SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2349664%22%20slang%3D%22en-US%22%3EJob%20Status%20Spreadsheet%20Display%20-%20Filtering%20for%20ALL%20or%20a%20restricted%20set%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2349664%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI'm%20using%20Microsoft%20365%20Excel%2C%20working%20with%20a%20simple%20worksheet%20for%20jobs%20running%20through%20a%20department.%20It's%20a%20simple%20log%20containing%20a%20Job%20Number%2C%20Customer%2C%20Description%2C%20Priority%2C%20Date%20Entered%2C%20Date%20Needed%2C%20Date%20Completed%2C%20and%20Job%20Owner.%3C%2FP%3E%3CP%3EI%20will%20be%20displaying%20this%20spreadsheet%20on%20a%20wall%20monitor%20for%20reference%20for%20those%20needing%20a%20status%20of%20the%20work.%20I%20need%20a%20way%20to%20display%20this%20information%20in%20two%20ways%3A%201)%20All%20rows%2C%20and%202)%20Only%20those%20rows%20where%20there%20is%20no%20completion%20date%20OR%20when%20the%20completion%20date%20is%207%20days%20or%20less%20from%20the%20current%20date.%3C%2FP%3E%3CP%3EUsing%20the%20Filter%20option%2C%20I%20can%20easily%20get%20just%20the%20blank%20rows%20to%20display%2C%20but%20have%20not%20been%20able%20to%20get%20the%20rows%20to%20display%20where%20DAYS%3DTODAY()-Date%20Completed%3D%26lt%3B7%2C%20and%20I%20need%20both.%3C%2FP%3E%3CP%3EI%20also%20can't%20figure%20out%20an%20easy%20way%20to%20toggle%20between%20displaying%20%22all%22%20and%20only%20the%20incomplete%20or%20recent%20completions.%3C%2FP%3E%3CP%3EFor%20extra%20points%2C%20when%20I%20complete%20a%20job%20with%20a%20priority%20of%201%2C%20I%20would%20like%20to%20auto-update%20%232%20to%20%231%2C%203%20to%202%2C%20etc.%3C%2FP%3E%3CP%3EAny%20and%20all%20help%20is%20greatly%20appreciated.%20I%20seem%20to%20get%20this%20screen%20display%20so%20close%2C%20but%20so%20far%20I%20can't%20get%20both%20conditions%20to%20work%20correctly....Blank%20completion%20date%20AND%20completion%20date%20within%207%20days%20from%20today.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2349664%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2349817%22%20slang%3D%22en-US%22%3ERe%3A%20Job%20Status%20Spreadsheet%20Display%20-%20Filtering%20for%20ALL%20or%20a%20restricted%20set%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2349817%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052388%22%20target%3D%22_blank%22%3E%40TechCenter1000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2350171%22%20slang%3D%22en-US%22%3ERe%3A%20Job%20Status%20Spreadsheet%20Display%20-%20Filtering%20for%20ALL%20or%20a%20restricted%20set%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2350171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052388%22%20target%3D%22_blank%22%3E%40TechCenter1000%3C%2FA%3E%26nbsp%3BUse%20this%20formula%20in%20the%20Days%20column%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOR(ISBLANK(I2)%2C(TODAY()-I2)%26lt%3B%3D7)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Band%20filter%20out%20FALSE%2C%20showing%20only%20TRUE.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2350772%22%20slang%3D%22en-US%22%3ERe%3A%20Job%20Status%20Spreadsheet%20Display%20-%20Filtering%20for%20ALL%20or%20a%20restricted%20set%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2350772%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E
New 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.