Help with conditional formatting

[nlege051]

I am working with 3 columns and I would like them to do the following:

1) when a date is entered in column F, I would like column G to calculate and add 5 business days to the date entered. (column G is the due date). If there is no date in column F, column G should stay blank.
2) I would like column G to turn red if the due date (column G) has passed. Column G should not turn red if column H (date of receipt) contains a date.

I managed to figure some of the conditional formatting I wanted but I am still having some difficulties getting all the formatting to work.

Thank you for your help.

 

[Special-K99]

1) Try

in G1
=IF(F1<>0,WORKDAY(F1,5),"")

2)

Select G1

Conditional Formatting
New Rule
Use a formula to determine...

=AND(G1>TODAY(),H1=0)
Format as red

Use Format painter (paintbrush icon) to copy to other cells

 

[nlege051]

Thanks for the quick response Special-K99
It worked for some cells but not for others.
Some cells aren't turning red when they should.
I would also like column G to always apply this formatting without having to copy it to other cells each time (if possible).

 

[Joe4]

Please provide example of values it is not working for.

I would also like column G to always apply this formatting without having to copy it to other cells each time (if possible).

Highlight the entire range you want to apply it to.
Then write the Conditional Formatting formula as it applies to the first cell in your selection. Excel is smart enough to adjust the formula for the other cells, provided you have use absolute/relative range references correctly, where necessary (if you are just going down column G, I don't think you need to worry about that).

 

[nlege051]

Please provide example of values it is not working for.


Highlight the entire range you want to apply it to.
Then write the Conditional Formatting formula as it applies to the first cell in your selection. Excel is smart enough to adjust the formula for the other cells, provided you have use absolute/relative range references correctly, where necessary (if you are just going down column G, I don't think you need to worry about that).

Some of the values that are not turning red (I checked to make sure the date format was the same, that the formula was added to the cells), and that I added the right rule in the conditional formatting section):

23-Aug-16	30-Aug-16
23-Aug-16	30-Aug-16
23-Aug-16	30-Aug-16
23-Aug-16	30-Aug-16
23-Aug-16	30-Aug-16

<tbody>
</tbody><colgroup><col><col><col></colgroup>

In addition, when I add additional dates in column G, it automatically turns them red even when the date has not passed.

 

[Joe4]

I used a little different variation of the Conditional Formatting Formula:

=AND(G1<>"",G1>TODAY(),H1="")

Note, you also didn't indicate whether the values in column H (if any) are the result of formulas or hard-coded values.

Listed below is an example that I tested (columns F-H starting in row 1). It looks like the correct two value turned red, according to your conditions.

[TABLE="width: 228"]
<tbody>[TR]
[TD="align: right"]08/01/2016[/TD]
[TD="align: right"] 08/08/2016[/TD]
[TD="align: right"]08/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]08/23/2016[/TD]
[TD="align: right"]08/30/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/31/2016[/TD]
[TD="align: right"]09/07/2016[/TD]
[TD="align: right"]08/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2016[/TD]
[TD="align: right"]09/08/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/15/2016[/TD]
[TD="align: right"]09/22/2016[/TD]
[TD="align: right"]08/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]09/20/2016[/TD]
[TD="align: right"][COLOR=#ff0000]09/27/2016[/COLOR][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/30/2016[/TD]
[TD="align: right"][COLOR=#ff0000]10/07/2016[/COLOR][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/01/2016[/TD]
[TD="align: right"]10/07/2016[/TD]
[TD="align: right"]08/01/2016[/TD]
[/TR]
</tbody>[/TABLE]

 

[nlege051]

the value in column H represents the date the information was received. This information is entered manually.

 

[Joe4]

OK. Did you try my formula?
Did you get the expected results?
Would you agree with the sample data I posted that the correct records were turned red? If not, please explain which ones you don't agree with and why.

If my example looks good but you are unable to get it to work for you, please tell us what range you are highlighting when entering in the Conditional Formatting formula, and what exactly you are typing in?

 

[nlege051]

OK. Did you try my formula?
Did you get the expected results?
Would you agree with the sample data I posted that the correct records were turned red? If not, please explain which ones you don't agree with and why.

If my example looks good but you are unable to get it to work for you, please tell us what range you are highlighting when entering in the Conditional Formatting formula, and what exactly you are typing in?

I started a new document and everything is working fine now. thank you.