Conditional Formatting

Occasional Contributor

Hello!

 

I need some help with a spreadsheet for my client and some conditional formatting.

 

I've attached a photo of the sheet to show what I'm talking about. I've set it up so when the date in column P is entered then it populates the date 30 days from then in column S, then column V and so on. When the date in column S is is met and up to 7 days overdue then it will highlight orange. When this date is 8 days or more overdue then it highlights red. I cant get the correct formatting to show this so any help would be greatly appreciated. 

 

The next problem which stems from this one is how do I get rid of the red box. It highlights red to show my client that he needs to get in touch with people every 30 days. So if its overdue and highlights red its to remind him to email but when he does it will still be red. Could I add another column in titled 'Sent' and he could add a Y in there when he sends that email. A formula could then be entered to then show if that box has Y then remove the conditional format. Open to ideas if there's an easier way.

 

Hope that makes sense and thanks very much!

9 Replies

Hi @KeiraW73 

You may use below formula in conditional formatting to achieve your requirements.

tauqeeracma_0-1616156663126.png

I have added a status column , if this column is filled no Red color will be displayed.

A sample file is also attached for your reference.

Please let me know if it works for you.

 

Thanks

Tauqeer

Hi@tauqeeracma 

 

This is perfect! Thank you so much for your help! I just couldn't figure out how to combine them both together.

 

Would you be able to have a look at my sheet and double check my formats for columns J-O. I feel I could condense them like you have but not entirely sure where to start.

It's set up so intro date is added and if it has been more than 30 days the reply column will fill red. If a reply is received and Y added the red will disappear and the next 4 columns show N/A. If a N is entered then the red also disappears and a 2nd intro date is added and so on. There is 3 formats for one cell which seems a lot. Hope that makes sense!

 

Thank you!!!!

I forgot to attach the sheet!

Hi @KeiraW73 

 

I have tried to modify the logic as per your explanation and that gives below results:

tauqeeracma_0-1616170250605.png

You may refer the attached file. 

 

Thanks

Yes that is perfect, thank you so much.

Last question, I promise! Is there a way to have the 1st email column (H) auto populate with a date? It will be a pain as it would need to take the date from either intro 1, 2 or 3 which ever had the latest date in it. So the first row it would be intro 1 as the rest are N/A. Second row it would be intro 2 and so on.

Hi @KeiraW73 

 

No issues, you may ask as many questions as required. As far as column H is concerned please use below formula:

 

=MAX(B5,D5,F5)

tauqeeracma_0-1616175823237.png

You may also refer to the attached file.

 

Thanks

 

 

Ok thank you. Only issue is I need the 1st email date to be 30 days later than the dates before it.

@KeiraW73 

 

Simply add 30 at the end of the formula =MAX(B5,D5,F5)+30

 

tauqeeracma_0-1616183653971.png

 

Thank you so much for your help! This is brilliant