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.

 

[eabaker64]

@Cubist I swear the other day when I tried this formula, it worked perfect, but now I am having problems with it. If the cells in B2 are blank, with no dates, the conditional formatting works fine. But for this spreadsheet, I am having to go back and enter past dates. For A2, I have a date of 9/20/24. B2, without a date entered, shows red, which it should. But if I enter a date in B2 that is 9/24/24 (within the three working days, excluding holidays), it still shows red and it should show green. The attached pic is actually my second attempt today. My first attempt had all the same information there, but the conditional formatting was not working at all. And at one point, I changed a date on A4 and it changed the fill color on B3...have no idea of how. My computer is possessed, or at least Excel seems to be. Thanks for taking a look at this for me.

 

[eabaker64]

@Cubist I swear the other day when I tried this formula, it worked perfect, but now I am having problems with it. If the cells in B2 are blank, with no dates, the conditional formatting works fine. But for this spreadsheet, I am having to go back and enter past dates. For A2, I have a date of 9/20/24. B2, without a date entered, shows red, which it should. But if I enter a date in B2 that is 9/24/24 (within the three working days, excluding holidays), it still shows red and it should show green. The attached pic is actually my second attempt today. My first attempt had all the same information there, but the conditional formatting was not working at all. And at one point, I changed a date on A4 and it changed the fill color on B3...have no idea of how. My computer is possessed, or at least Excel seems to be. Thanks for taking a look at this for me.

Actually nevermind. I got it figured out. Sorry.