conditional formatting to ignore weekends

kingchub

New Member
Joined
May 19, 2015
Messages
3
Hi i have a spreadsheet with some dates on it.

I will try to explain it a bit so please bear with me.

I have dates in columns F and G. i have conditional formatting set in column F that colours those cells Red if they were more than 3 days ago.

for that i am using the Rule: Format only cells with, cell value, less than, =NOW()-3 and to fill red in this case.

I then have another condition that overides the first if there is something written in the corresponding cell for Column G. Here i use the rule: Format values where this formula is true, =ISBLANK(G1)=FALSE and ticked stop if true.

so to be clear up to this point. there are dates in column F. The cells in column F will be red if the date in them is more than 3 days ago. Unless there is something (a date) written in column G.


The dates in column G also have conditional formatting. They will turn Red if the date in column F is more than 3 days ago. They will turn green if the date in column F is 3 days or less ago.


Now this works well however i would like weekends and holidays to be left out of the calculation of the days between the dates in column F and G.


Can you please help?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Replace the =NOW()-3 with: =WORKDAY(NOW(),-3,holidays) where "holidays" is a named range where you've entered the holidays.
 
Upvote 0
hi again following on from this. I now want the cells in column G to fill red if the date in column F is more than 3 working days past.

for example

F G

14/05/2015 19/05/2015

i would want the above in column G to fill red if there are more than 3 working days between the column F value and the column G value.

im thinking something along the lines of =NETWORKDAYS(G2,F2)>3 whcih is under format values where this formula is true: under Use a formula to determine which cells to format in Conditional Formatting, with fill red.

but its not working. some values correctly show red others do not.

pls help.
 
Upvote 0
The syntax is =NETWORKDAYS(starting date, ending date), so in your example G2 is higher than F2 so the result is a negative 4. So, if Col G will always be higher than Col F, just switch the F2 and G2 in your formula. If this is not the case and you want to highlight anything with a difference of more than 3 workdays, try this conditional format formula:
Code:
=OR(NETWORKDAYS(G1,F1)<-3,NETWORKDAYS(F1,G1)<-3)
One other comment to clarify, if you want to set all the cells in Col G to be conditionally formatted, 1st select Col G and then start the conditional formatting process using G1 and F1 in your formula. If you want to CF only G2:G10, then select G2:G10 and use G2 and F2 in your formula.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top