Basic Conditional Formatting

[Wardylewis]

Good Afternoon,

I am currently attempting to update a spreadsheet with conditional formatting. However I am becoming stuck with a simple problem.

We have a column which has the date a letter is received which starts at E7 we then have another column which states when a letter has been responded to which starts in F7.

Basically I have managed to set the sheet to say if E7 date received exceeds 14 days to turn red. What I am after now is that if any date is entered in F7 then the conditional formatting stops.

I hope somebody can help.

Lewis.

 

[Joe4]

Try a Conditional Formatting formula like:

=AND(F7="",TODAY()-E7>14)

 

[Wardylewis]

Try a Conditional Formatting formula like:

=AND(F7="",TODAY()-E7>14)

Hello. This works perfectly on the first cell. How do i get this to duplicate going downwards to row 32.

 

[Joe4]

Select all the cells that you wish to apply this to.
And then write the formula as it applies to the FIRST cell in your selection. Excel will automatically adjust it for the other cells.

 

[Wardylewis]

Select all the cells that you wish to apply this to.
And then write the formula as it applies to the FIRST cell in your selection. Excel will automatically adjust it for the other cells.

Perfect that works brilliantly. However I have now stumbled across another issue.

I am using a formula to pull information through =INDIRECT($B16&"!$R$4") this pulls through the data that relates to a page bearing that columns B information.

However it means when no date is added this returns a date of 00 January 1900 i can make this go blank however this prevents the conditional formatting for working. Is there anything I can do?

Lewis

 

[Joe4]

However it means when no date is added this returns a date of 00 January 1900 i can make this go blank however this prevents the conditional formatting for working. Is there anything I can do?

How are you making it go blank?
What do you want to happen to these values?

 

[Wardylewis]

How are you making it go blank?
What do you want to happen to these values?

Im using the following =INDIRECT(($B16&"!$R$4"),"")

I just want the cell to go red if the date inputed exceeds 14 days but stop after they have submitted a reply.

It may need to be that it only stops if the date submitted (E7) date is greater than the date recieved (F7)

Lewis

 

[Joe4]

But is this formula in the E7 or F7 space?
What I want to know is what do you want to happen when this INDIRECT formula returns a blank. Should it trigger the Conditional Formatting or not in this particular case.

Also, I don't think you are using the INDIRECT formula correctly. I don't think the second argument is what you think it is (where you are returning "").
It is looking for a Boolean value. See: https://exceljet.net/excel-functions/excel-indirect-function

 

[Wardylewis]

But is this formula in the E7 or F7 space?
What I want to know is what do you want to happen when this INDIRECT formula returns a blank. Should it trigger the Conditional Formatting or not in this particular case.

Also, I don't think you are using the INDIRECT formula correctly. I don't think the second argument is what you think it is (where you are returning "").
It is looking for a Boolean value. See: https://exceljet.net/excel-functions/excel-indirect-function

E7 =INDIRECT($B7&"!$G$4")
F7 =INDIRECT(($B16&"!$R$4"),"")

I could change it so that if a cell return the text value 00 January 1900 to turn the cell blank however this still stops the orgional conditional formatting from working as it still returns a value. I just need E7 to remain red till a valid date is entered.

Lewis

 

[Joe4]

So it sounds like this latest issue is when E7 is return this blank field. Is that right?
If so, should be able to update CF formula to:

=AND(F7="",TODAY()-E7>14,E7>0)

Which will also check to make sure that sure E7 is greater than zero.

If we also need to address F7, to treat a zero the same as a blank, then maybe:

=AND(OR(F7="",F7=0),TODAY()-E7>14,E7>0)