Highlight Cells Tomorrow Excluding Weekend

Question asked by user @ Excel forum:-

How to highlight cells tomorrow excluding weekend through Conditional formatting?

I have dates entered manually down the C column and i use the standard highlight rule to color the cells i.e. today. Tomorrow and Yesterday. Now my issue is come Friday in the week I wish to have Mondays date highlighted as Tomorrow so it has excluded the weekend. The standard conditional formatting for when date occurs won’t work in this instance and I have tried at least 20 formulas from forums etc.
What am I doing wrong? Or is there a simpler way? I just wish it to not count any weekends and make Friday 1 and Monday the day after so it will highlight as if it was tomorrow.

image 1

 

To resolve this problem, Our Expert has used the combinations of formulas:- “TODAY”, “WORKDAY” and “IF” functions.

“TODAY” function will help to return the today date.

“IF” function will help to check the condition and then will perform accordingly.

“WORKDAY” function will help to return the serial number of the date before or after a specified number of workdays.

To highlight the tomorrow date we will use the combination of formula in conditional formatting option, follow below given steps:-

  • Go to Home tab, select “Conditional Formatting” under the Styles group and select “New Rule”.

image 2

 

  • Select the range of Date.
  • “New Formatting Rule” dialog box will get appear.
  • Click on “Use a formula to determine which cells to format”.
  • Then enter the formula:- =IF(TODAY()+1=WORKDAY(B2,0,2),TODAY()+1,"")

 

image 3

 

  • Tomorrow date will get highlighted, but if there is weekend tomorrow then it will not be highlighted.

image 4

 

Main Page

Conditional Format Based on Dates

Find Occurrence of Text in a Column

How to Highlight a row on the basis of Cell

Compare 2 Columns and Return Fill Red if is different

How to check the row and then highlight the first cell of the row

Highlight Cells Tomorrow Excluding Weekend

Conditional Formatting to Mark Dates on a Calendar

How to apply Conditional Formatting  in a Cell before a Particular Character

Highlight the Top 10 Sales through Conditional Formatting

Conditional Formatting for Pivot Tables

Conditional Format Between First and Last Non-Blank Cells

PDF

Excel

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube