CF to colour cells that show a date approaching todays date

I am looking for a formula that will allow me to shade cells that show a date that is within a week away from todays date.

I basically have a spreadsheet with multiple columns and data, one of which is a project starting date, I need to be able to see when the project is within a week away from beginning. I have hundreds of project so there are many dates, therefore I am looking to use CF to help me.

I already have a formula in place to highlight cells that show a date that has already gone past todays date:

Formula:

Please Login or Register  to view this content.

but I am unsure on how to alter this further, I have tried adding in "-7" and "+7" in various places but it does not seem to work.

If you could help me I would be very grateful and obviously would Add to your Reputation.

Thanks in advance

Hello Alexander try this =AND(MONTH($H3)=MONTH(TODAY()),H3-TODAY()>=1,H3-TODAY()<=7)

Originally Posted by hemesh

Hello Alexander try this =AND(MONTH($H3)=MONTH(TODAY()),H3-TODAY()>=1,H3-TODAY()<=7)

Perfect first time! Thank you very much!

You are welcome Alexander