Conditional Formatting with 3 criteria

[Mike G.]

Hello,

I have 1 column of dates. If that date has passed I'd like to turn the cell red. If that date is within 1 week (today + 1 week) I'd like to turn the cell yellow. Lastly if the cell is blank, I'd would like to ignore it until that cell is inputted with a date.

Any help would be greatly appreciated.

Thank you.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[AliGW]

You need two rules, one for each criterion:

=AND(A1<>"",A1 < TODAY())

=AND(A1<>"",A1 > =TODAY(),A1 < TODAY()+7)

I'm assuming your dates start in A1.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. Windows 10 is your OS, NOT your Office version! If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[Mike G.]

Thank you so much! Your suggestions worked great. I appreciate you.

[AliGW]

Please update your profile, as instructed earlier.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Mike G.]

I just marked as solved and added to your reputation. thanks again!

[AliGW]

But you still haven't updated your profile, which is more important, really.

Thanks for the rep.