Condition Formatting After 30 Days Have Passed.

[HomieShipSteve]

Hi all,

Thank you for taking the time to read this and try to help me with my query.

I have a spreadsheet to record all the jobs that have been created and done for my property maintenance company. However, I would like to add in conditional formatting to show which jobs haven't been done within 30 days. There is a start date column which I fill in with the date that I received the job. I also have a completed date column which I input the date in which the job was completed. What I wish to do is use conditional formatting to change the start date cell to red, if there has been nothing inputted into the complete date within 30 days. This will help to show which jobs are more urgent than others.

Many thanks

 

[steve the fish]

Hi. With your start dates in column A and your end dates in B click column B then press conditional formatting-new rule-use a formula

=AND(A1<>"",B1="",TODAY()-30>A1)

 

[HomieShipSteve]

Hello and thank you for the reply

I created a new excel sheet to test this. However, when I created two dates more than 30 days apart in column A and B, the formula had no effect. I changed the format to fill red to indicate whether or not it was over 30 days, but nothing occurred.

Thank you for your help!

 

[steve the fish]

Thats because its not designed to

If you have completed the job then what concern is it to which one is priority? The formula checks column A has something in it, ie the date started, and column B is empty, ie hasnt been finished.

 

[HomieShipSteve]

Just one more question, is there a way that I can spread this condition over multiple cells, with it editing the cell numbers for me, or do I have to do it all by hand?

Thank you so much for all of your help!

 

[steve the fish]

The way i explained and using the formula i provided means it works for every cell in column B not just one. Its the 'applied to' box that tells the CF which cells to format but your formula must refer to the top left cell in that range.