• 465K Members
• 10.8K Online
• 562K Conversations
SOLVED

New Contributor

# Highlighting a row based on the day of the week

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

# Re: Highlighting a row based on the day of the week

Hi Christopher,

Perhaps

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

# Re: Highlighting a row based on the day of the week

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!

# Re: Highlighting a row based on the day of the week

I imitated it on this model

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

# Re: Highlighting a row based on the day of the week

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 difference

Solution

# Re: Highlighting a row based on the day of the week

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()

# Re: Highlighting a row based on the day of the week

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!

# Re: Highlighting a row based on the day of the week

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
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies