SOLVED
Home

Highlighting a row based on the day of the week

%3CLINGO-SUB%20id%3D%22lingo-sub-617824%22%20slang%3D%22en-US%22%3EHighlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-617824%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20first%20post%20in%20this%20forum%20and%20I%20just%20have%20a%20quick%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20have%20an%20excel%20document%20set%20up%20to%20highlight%20the%20next%20day's%20jobs%20by%20using%20%22%3D%24I9%3DTODAY()%2B1%22.%20However%2C%20when%20Friday%20comes%20around%20I%20would%20like%20to%20highlight%20Monday%20instead%20of%20not%20having%20anything%20highlighted.%20I%20tried%20messing%20around%20using%20the%20IF%20and%20Weekday%20functions%2C%20but%20I%20couldn't%20get%20it%20to%20work.%20I%20was%20using%20the%20IF%2FWeekday%20Functions%20in%20order%20to%20try%20and%20subtract%20days%20from%20the%20total%20(Friday%20-%204%20%3D%20Monday%20etc)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20very%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EChristopher_P%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-617824%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-618177%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-618177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345245%22%20target%3D%22_blank%22%3E%40Christopher_P%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Christopher%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%3E%3D%24I9%3DTODAY()%2BCHOOSE(WEEKDAY(TODAY()%2C2)%2C1%2C1%2C1%2C1%2C3%2C2%2C1)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-618179%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-618179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attempted%20to%20insert%20that%20into%20the%20sheet%20that%20I%20was%20using%2C%20and%20it%20only%20registered%20one%20out%20of%20six%20jobs%20that%20it%20should%20of%20highlighted%2C%20as%20well%20as%20a%20few%20rows%20that%20don't%20even%20have%20a%20date%20on%20them!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-618413%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-618413%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345245%22%20target%3D%22_blank%22%3E%40Christopher_P%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20imitated%20it%20on%20this%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20318px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114517iE2A88304DCE9DE40%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhere%20A1%20is%20instead%20of%20TODAY()%20-%20it%20works.%20How%20your%20data%20is%20structured%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-618870%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-618870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20it%20set%20up%20in%20the%20I%20column%20as%20mm%2Fdd%2Fyyyy.%20There%20are%20empty%20rows%20between%20each%20entry%20so%20that%20it%20is%20easier%20for%20the%20owner%20to%20read.%26nbsp%3B%20Below%20is%20a%20screengrab%20from%20how%20I%20have%20the%20entries%20listed.%20The%20highlighting%20at%20the%20bottom%20was%20done%20by%20me%20as%20I%20was%20working%20on%20it%2C%20it%20does%20not%20relate%20to%20the%20issue.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114558i1D69699D33E74986%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Workdays.png%22%20title%3D%22Workdays.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-619141%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-619141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345245%22%20target%3D%22_blank%22%3E%40Christopher_P%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24I%3A%24I%2CMATCH(TODAY()%2BCHOOSE(WEEKDAY(TODAY()%2C2)%2C1%2C1%2C1%2C1%2C3%2C2%2C1)%2C%24I%3A%24I%2C0))%3D%24I1%3C%2FPRE%3E%0A%3CP%3ESee%20Sheet2%20attached%20where%20A1%20is%20instead%20of%20TODAY()%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-623101%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-623101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked%20on%20your%20document%2C%20I%20copied%20it%20over%20to%20my%20work%20document%2C%20added%20%3DTODAY()%20in%20A1%2C%20and%20inserted%20the%20formula.%20The%20attached%20file%20is%20what%20happened%20-%20basically%2C%20it%20was%20highlighting%20the%20row%20below%20the%20date%20of%20tomorrow%2C%20instead%20of%20highlighting%20the%20actual%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20I%20changed%20the%20cell%20that%20it%20was%20referencing%20from%20I9%20to%20I10%20and%20it%20fixed%20itself!%20Thank%20you%20so%20much%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624223%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20a%20row%20based%20on%20the%20day%20of%20the%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624223%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345245%22%20target%3D%22_blank%22%3E%40Christopher_P%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20far%20so%20good%2C%20but%20better%20if%20you%20use%20I1%20with%20INDEX(I%3AI%2C..%20It%20doesn't%20matter%20what%20is%20in%20your%20first%208%20cells.%20If%20your%20range%20(I%3AI)%20starts%20from%20first%20row%2C%20start%20comparing%20from%20first%20row%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Christopher_P
New Contributor

Hello everyone! 

 

This is my first post in this forum and I just have a quick question.

 

Currently I have an excel document set up to highlight the next day's jobs by using "=$I9=TODAY()+1". However, when Friday comes around I would like to highlight Monday instead of not having anything highlighted. I tried messing around using the IF and Weekday functions, but I couldn't get it to work. I was using the IF/Weekday Functions in order to try and subtract days from the total (Friday - 4 = Monday etc)

 

Any suggestions would be very much appreciated.

 

Thank you,

Christopher_P

7 Replies

@Christopher_P 

Hi Christopher,

 

Perhaps

=$I9=TODAY()+CHOOSE(WEEKDAY(TODAY(),2),1,1,1,1,3,2,1)

@Sergei Baklan 

 

Sergei,

 

I attempted to insert that into the sheet that I was using, and it only registered one out of six jobs that it should of highlighted, as well as a few rows that don't even have a date on them!

@Christopher_P 

I imitated it on this model

image.png

where A1 is instead of TODAY() - it works. How your data is structured?

@Sergei Baklan 

I have it set up in the I column as mm/dd/yyyy. There are empty rows between each entry so that it is easier for the owner to read.  Below is a screengrab from how I have the entries listed. The highlighting at the bottom was done by me as I was working on it, it does not relate to the issue. 

 

EDIT: I also have a higher priority rule already in place for the N/A rows, if that makes a differenceWorkdays.png

Solution

@Christopher_P ,

 

When it could be

=INDEX($I:$I,MATCH(TODAY()+CHOOSE(WEEKDAY(TODAY(),2),1,1,1,1,3,2,1),$I:$I,0))=$I1

See Sheet2 attached where A1 is instead of TODAY()

@Sergei Baklan 

 

It worked on your document, I copied it over to my work document, added =TODAY() in A1, and inserted the formula. The attached file is what happened - basically, it was highlighting the row below the date of tomorrow, instead of highlighting the actual date. 

 

EDIT: I changed the cell that it was referencing from I9 to I10 and it fixed itself! Thank you so much for your help!

Highlighted

@Christopher_P ,

 

So far so good, but better if you use I1 with INDEX(I:I,.. It doesn't matter what is in your first 8 cells. If your range (I:I) starts from first row, start comparing from first row as well.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies