Exclude Weekends in formula

[linganit]

Hi

How do I exclude weekends on the following conditional formatting formula:
=IF(F4-B4<=7,TRUE). F4 and B4 are the two columns with dates in.

Thanks

[AliGW]

Have you explored the NETWORKDAYS() function?

[linganit]

Hi Ali

I tried, it didn't work. I am not sure where exactly to put it. Will you please assist me.

Thanks

[AliGW]

If you can provide a workbook for me, yes, I'll try.

EDIT Try this:

=IF(NETWORKDAYS(F4,B4)<=7,TRUE)

[Tony Valko]

Maybe this...

=NETWORKDAYS(B4,F4)<=7

[linganit]

Hi Ali & Tony

Thanks.

I tried =NETWORKDAYS(B4,F4)<=7 and it seems to work however it seems to ignore the times as well. For example if an incident is captured @13h00 on 30 May, 7 days is on 8 June @13h00 (excluding weekends), therefore anything after 13h00 on 8 June is over 7 days, hence it should be red.

Please check the conditional formatting in column F, that is the one I have updated with the formula you suggested.

Thanks

[AliGW]

This has moved quite a way from what you asked for at the beginning! I'm afraid I'll have to bow out on this one: dates and times in Excel are not my strong point, sorry!

[linganit]

Thanks for your help Ali. Apologies for not being specific enough.

[jason.b75]

Try

=AND(NETWORKDAYS(B4,F4)<=7,MOD(B4,1)>=MOD(F4,1))

Checks that the difference is less than 7 working days, and that the time in F4 is earlier than the time in B4.

[linganit]

Thanks Jason.

The formula works however after 12pm it doesn't. Any ideas why its doing that?

[jason.b75]

Not sure what I was thinking with that formula, it looked good at the time. It fails when the time in B4 is earlier than the time in F4 and the difference in days excluding the time part is less than 7.

This one is better

=(NETWORKDAYS(B4,F4)+MOD(B4,1)-MOD(F4,1))<7

[linganit]

Hi Jason

Thanks. I am however not sure what it is I am doing wrong. Please check the conditional formatting rules in column F (I have attached the file). What I am trying to do is conditional format the cell green if the date and time is less than 7 days, amber if it is 8 days and red if it is 9 days (the date I am comparing to is in column B).

Please help.

[jason.b75]

That worksheet still has the formula from post #9 in the conditional formatting, you need to update it to the formula in post #11 for it to work.

That formula is for the green condition, change <7 to <9 for yellow, and >=9 for red.

The 'Stop if true' boxes all need to be checked.