Conditional formatting

[eabaker64]

I have looked online to see if I could get this figured out on my own, but I have been unsuccessful in finding exactly what I am looking for. I have a tracking spreadsheet. Column A contains a date that a list was created, Column B will have a date entered for when the list was sent to the provider. I have it formatted so if there is no date in column A, both columns have no formatting colors. If Column A has a date, I want column B to show red fill/red font if 4 working days (excluding holidays) have passed since the date in Column A. I want column B to show red even if it is blank (no date) but it is past the 4 working days. If it is less than 4 working days, I want column B to show green fill/font, whether column B has a date in it or not.

I was able to get one formula to work, but it does not look at workdays, and I hadn't decided to exclude holidays at that point. My holidays are listed in cells B34:B54

What I have so far is:
Formula: =IF(TODAY()<$B$2+3,1,0) Green fill with green font
Formula: =IF($B$2<TODAY()+4,1,0) Red fill with red font
Formula: =$B$2="" No formatting

I know that I need the WORKDAY function in there somewhere, and I believe I need the TODAY function in there as well, but I have no idea of what should go where. Any help is greatly appreciated!!

 

[Cubist]

See if this works for you:

Book1
	A	B	C	D	E
1	Date1	Date2		Holidays	Date
2	11/14/24	11/27/24		New Year's Day (January 1)	1/1/24
3	11/8/24			Juneteenth National Independence Day (June 19)	6/19/24
4	11/12/24			Independence Day (July 4)	7/4/24
5				Veterans Day (November 11)	11/11/24
6				Thanksgiving Day (Fourth Thursday in November)	11/25/24
7				Christmas Day (December 25)	12/25/24
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B5	Expression	=(A2<>"")*(TODAY()-WORKDAY.INTL(A2,4,1,$E$2:$E$7)>=4)	text	YES
B2:B5	Expression	=(A2<>"")*(NETWORKDAYS.INTL(A2,B2,1,$E$2:$E$7)>=4)	text	NO
B2:B4	Expression	=(A2<>"")*(TODAY()-WORKDAY.INTL(A2,4,1,$E$2:$E$7)<4)	text	NO

 

[eabaker64]

See if this works for you:

Book1
	A	B	C	D	E
1	Date1	Date2		Holidays	Date
2	11/14/24	11/27/24		New Year's Day (January 1)	1/1/24
3	11/8/24			Juneteenth National Independence Day (June 19)	6/19/24
4	11/12/24			Independence Day (July 4)	7/4/24
5				Veterans Day (November 11)	11/11/24
6				Thanksgiving Day (Fourth Thursday in November)	11/25/24
7				Christmas Day (December 25)	12/25/24
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B5	Expression	=(A2<>"")*(TODAY()-WORKDAY.INTL(A2,4,1,$E$2:$E$7)>=4)	text	YES
B2:B5	Expression	=(A2<>"")*(NETWORKDAYS.INTL(A2,B2,1,$E$2:$E$7)>=4)	text	NO
B2:B4	Expression	=(A2<>"")*(TODAY()-WORKDAY.INTL(A2,4,1,$E$2:$E$7)<4)	text	NO

I used the formula you suggested. The only thing I am not sure about is if the date entered in A2 is 11/12/2024, I would think it should show as red, but it is showing green. Even if it doesn't count the day of 11/12/24, starting with 11/13/24, 4 working days would be 11/18/24. With today being 11/19/24, shouldn't the cell be red?

 

[Cubist]

Flaws in my logic. Try:

Book1
	A	B	C	D	E
1	Date1	Date2		Holidays	Date
2	11/14/24	11/21/24		New Year's Day (January 1)	1/1/24
3	11/8/24			Juneteenth National Independence Day (June 19)	6/19/24
4	11/12/24			Independence Day (July 4)	7/4/24
5	11/18/24			Veterans Day (November 11)	11/11/24
6				Thanksgiving Day (Fourth Thursday in November)	11/25/24
7				Christmas Day (December 25)	12/25/24
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B5	Expression	=(A2<>"")*(TODAY()>=WORKDAY.INTL(A2,4,1,$E$2:$E$7))	text	NO
B2:B5	Expression	=(A2<>"")*(NETWORKDAYS.INTL(A2,B2,1,$E$2:$E$7)>=4)	text	NO
B2:B5	Expression	=(A2<>"")*(TODAY()<WORKDAY.INTL(A2,4,1,$E$2:$E$7))	text	YES

 

[Joe4]

Just wanted to make an educational comment about the formulas you used initially, like this one:

=IF(TODAY()<$B$2+3,1,0)

Conditional Formatting uses Boolean values (TRUE/FALSE) in determining whether or not to perform the action.
So it is not necessary to use an IF statement - just write your formula so it returns a boolean value.
For your formula above, you just need the part in the first argument of your IF function, i.e.

=TODAY()<$B$2

That is all you need to write! No IF formulas necessary!

 

[eabaker64]

Flaws in my logic. Try:

Book1
	A	B	C	D	E
1	Date1	Date2		Holidays	Date
2	11/14/24	11/21/24		New Year's Day (January 1)	1/1/24
3	11/8/24			Juneteenth National Independence Day (June 19)	6/19/24
4	11/12/24			Independence Day (July 4)	7/4/24
5	11/18/24			Veterans Day (November 11)	11/11/24
6				Thanksgiving Day (Fourth Thursday in November)	11/25/24
7				Christmas Day (December 25)	12/25/24
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B5	Expression	=(A2<>"")*(TODAY()>=WORKDAY.INTL(A2,4,1,$E$2:$E$7))	text	NO
B2:B5	Expression	=(A2<>"")*(NETWORKDAYS.INTL(A2,B2,1,$E$2:$E$7)>=4)	text	NO
B2:B5	Expression	=(A2<>"")*(TODAY()<WORKDAY.INTL(A2,4,1,$E$2:$E$7))	text	YES

PERFECT!!! And thank you for the other information about not needing the IF function. So much appreciated. And I never would have come up with the formula for the red and green cells, you have saved me SO much time.

 

[Joe4]

eabaker64,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.