Conditional Formatting Troubleshooting

Question

Hi everyone!

I need some help! My team and I have been using Google Sheet for years and the company is having us switching over to Excel, specifically in teams/online (No Apps).

We have a spreadsheet of tasks that gets updated by our staff, in several columns and we used to have conditional formatting that could easily help us understand if something needs to be done/attended to.

The staff would change the date in a cell for the last time they added a note into the system and then the conditional formatting could be: Green if it was put in within the last week, Yellow if it was between a week and two weeks old and red if it was over two weeks old. This helped us easily see who needed to add notes at a glace.

I Have been trying to get this done in excel for over a month at this point, look online for examples and have found some things that are promising but never seem to actually get the job done!

I currently have a sheet where the cells change color but the reason why it changes seems unclear and some cells don't change color at all even though they should... so clearly something is wrong.

Would anyone be able to help me with getting this sort of functionality running for my team? otherwise we will be going back to the google sheets!

Answer 1

Select the range in which the users will enter the date.

1. On the home tab of the ribbon, click Conditional Formatting > New Rule...
   Select 'Format only cells that contain'.
   Leave the first drop down set to 'Cell Value'.
   Select 'less than or equal to' from the second drop down.
   In the box next to it, enter the formula

=TODAY()

Click Format...
Activate the Fill tab.
Select green as highlight color.
Click OK, then click OK again.

2. Repeat these steps, but with the formula

=TODAY()-8

and yellow as fill color.

3. Repeat them again with the formula

=TODAY()-15

and red as fill color.

4. Finally, repeat them with 'equal to', with the formula

=""

and No Color.