conditional formating excel

Ace Modder · 17 Oct 2006 at 19:34

I have a spreadhseet with a list of orders, and the delivery dates on them.

I have set the conditional formatting to "Cell" "equal to" "=TODAY()" to show the order in red.

I have set the conditional formatting to "Cell" "equal to" "=TODAY()+1" to show the order in orange.

I have set the conditional formatting to "Cell" "equal to" "=TODAY()" to show the order in green.

I need to set the conditional formatting to only inclde weekdays....

NOT WEEKENDS DAYS. so on Monday show friday as orange, and thursday as red

Any ideas how?

arcamalpha · 17 Oct 2006 at 19:43

Add a nested IF statement in might do the job, but a Macro might be required to achieve what you want. Top of my head:

First a column that checks what day of the week the date is - like.
=WEEKDAY(delivery date cell).

Then a nested if to do something with the value.

IF(cell="SAT","n/a", IF(cell="SUN", "n/a", IF(cell=TODAY(), "red", IF(.....

Then set the conditional formatting to look at the cell value (which will only have 4 values "n/a", "red"etc).

arcamalpha · 17 Oct 2006 at 20:01

Thought I'd knock up a sample - you can tell I'm bored...

http://www.poopheads.co.uk/excel/

Ace Modder · 18 Oct 2006 at 07:03

I may not have explained myself very good.

What I need is.

We deliver on Monday - Friday. We have to pick the goods 2 working days before.

So I want to open the spreadsheet on Monday and have all of Wednesdays del dates in red, Tuesdays del dates in orange, and mondays del dates in Green. which is fine using the conditional formatting.

But when it comes to Thursday, I want it to find all of Mondays del dates in red, and Fridays dates in orange and thursdays in green.

On Friday I want all on Tuesdays del dates in red, mondays i want orange and fridays i want in green.

Thats the problem I have.

I have tried to use "=TODAY()+2WORKDAY" But that does not work.

arcamalpha · 18 Oct 2006 at 15:14

Well that's okay - just change the IF statements to check how many working days in advance. My example just checks for 0days (red), 1 days (orange) and >1days (green).

You might need an extra IF that finds the next working day rather than just the next day.. This will deal with weekends.

Not that hard!!

CF93 · 18 Oct 2006 at 15:48

[edit] ignore me [/edit]

Ace Modder · 18 Oct 2006 at 17:29

arcamalpha said:
Well that's okay - just change the IF statements to check how many working days in advance. My example just checks for 0days (red), 1 days (orange) and >1days (green).

You might need an extra IF that finds the next working day rather than just the next day.. This will deal with weekends.

Not that hard!!

and how do I do that?

Tomsk · 18 Oct 2006 at 19:41

I could post the complete solution for you but I won't. Instead I'll try and teach you how to do it.

Firstly you will need a couple of holding cells to represent how many days you need to look ahead - name these cells using the Insert -> Name -> Define function as DAY1 and DAY2 to represent the current day plus one working day and current day plus two working days.

In these cells using the WEEKDAY() function and a nested IF you will create a formula that checks what day TODAY() is and sets the cell value to be the number of days you need to add. ie if TODAY() is Monday, then DAY1=1 and DAY2=2, IF TODAY() is Thursday, DAY1=1, DAY2=4 etc

You then change the conditional formatting formula that I gave you in your previous thread from TODAY()+1 -> TODAY()+DAY1 and TODAY()+2 -> TODAY()+DAY2.

dan^uk · 19 Oct 2006 at 00:27

In a blank area of your worksheet enter the following formulas (I have used cells A1 to A3)..

Set Cell A1 as:-
=Today()

Set Cell A2 as:-
=A1+(WORKDAY(A1,1)-A1)

Set Cell A3 as:-
=A2+(WORKDAY(A2,1)-A2)

Then set the conditional formatting on your dates to:-

I think this will achieve what you want..