Excel help please - Conditional formatting, red, amber dates

[IsItPossibleToDoThis]

Hi,

Can someone help me please 🙏

I have a spreadsheet and it has column i: X date
I have column J: X due

I already have a formula (=i4+42) in column J and a cell at the top that has =today() in it to add the due date in.

I want column J to turn amber if the due date is in 7 days, stay amber, until the day after, when it turns red as it is overdue.

I can get J to turn r if overdue but I can't make it do the amber bit and all the instructions I'm getting aren't working.

Thank you!

Some cells are amber but they are not due within 7 days, they are due in August 😫

If column J has a formula instead of a direct value, you can modify the conditional formatting formulas to consider the formula result instead. Here's how you can adjust the conditional formatting formulas for column J:
For turning cells amber if the due date is within 7 days from the current date:

1. Select the range of cells in column J where you want the conditional formatting to apply.
2. Go to the "Home" tab in the Excel ribbon.
3. Click on the "Conditional Formatting" button in the "Styles" group, and then select "New Rule" from the dropdown menu.
4. In the "New Formatting Rule" dialog box, select the option "Use a formula to determine which cells to format."
5. In the "Format values where this formula is true" field, enter the following formula:

1. =AND(I1-TODAY()>=0, I1-TODAY()<=7, J1="")
2. This formula checks if the difference between the date in column I and the current date (TODAY) is between 0 and 7 days and if the cell in column J is empty.
3. Click on the "Format" button to choose the formatting style for cells that meet the condition. In this case, select the "Fill" tab and choose an amber color.
4. Click "OK" to close the "Format Cells" dialog box.
5. Back in the "New Formatting Rule" dialog box, click "OK" to apply the conditional formatting rule.

Now, the cells in column J will turn amber if the due date is within 7 days from the current date and the cell in column J is empty. For turning cells red if the due date has passed:

1. Select the range of cells in column J again.
2. Click on the "Conditional Formatting" button in the "Styles" group, and select "New Rule" from the dropdown menu.
3. In the "New Formatting Rule" dialog box, select the option "Use a formula to determine which cells to format."
4. In the "Format values where this formula is true" field, enter the following formula:

12.=AND(I1-TODAY()<0, J1="")

1. This formula checks if the difference between the date in column I and the current date (TODAY) is negative, indicating that the due date has passed, and if the cell in column J is empty.
2. Click on the "Format" button to choose the formatting style for cells that meet the condition. In this case, select the "Fill" tab and choose a red color.
3. Click "OK" to close the "Format Cells" dialog box.
4. Click "OK" in the "New Formatting Rule" dialog box to apply the conditional formatting rule.

Now, the cells in column J will turn amber if the due date is within 7 days from the current date and the cell in column J is empty. They will turn red if the due date has passed and the cell in column J is empty.

You need

< TODAY()

For the red overdue and

=AND(J2>TODAY(), J2<=TODAY() +7))

for the amber

Thanks, that hasn't worked.

Thank you.

That highlights one cell on the whole sheet amber, where col I is emptey and col J just has 11/02/00.

Is it easier to do in excel sheets? I think we are going to move the sheet from excel to sheets as its easier to have multiple people work on it.

How about these? - mine are for future dates but you could amend the formulae to suit you. The formats are applies to a single column

Sorry it looks a mess. You can see the individual pics when you click on them!

Are you deleting all the rules that aren't working? Those formulas were copied directly from a sheet I have which does the same thing and works

I've done it on google sheets using this page
https://sheetaki.com/highlight-expiration-due-dates-google-sheets/

and this conditional formatting

I'm just going to try these in excel now.

Yes

Go into "conditional formatting" and clear all rules.
Then create new rule.
Under "select a rule type" choose "Format only cells that contain". In the equation builder below change the between to "less than or equal to" and input the cell reference where your today()+7 value is. Set the format of this one to amber.
Then create another new rule, same thing again but input the cell reference where today() is and set the format of this one to red.
Make sure it has locked the cell for the today() and today()+7 values, otherwise it will move down a row as it goes down the table and might explain why you're getting odd results. So if today() value is in cell K2 for example, it should read $K$2 in your conditional formatting equation.

I haven't got a today()+7 formula anywhere

Then make one next to today()

That will make my 'todays date' 7 days in the future though.

Take a look at the attachment I posted earlier in the thread.

You do want a field that says today + 7 days because that's your amber window.

Then with the conditional formatting

Red is everything greater than todays date

Amber is everything between today and today + 7

Green is everything that's greater than today + 7

ahhh so i need to base dates, of sorts.

Thats worked! Thank you!

Except its putting things 7 days afetr today in amber, I want the 7 days before the due date to be amber. I changed it from +7 to -7 and it makes those cells red.

I've had enough of this for today. I'll sort it out on Monday and push to get us switched to google sheets.

Thank you everyone for your help.

Sorry typing in my car while I wait for DD so don't have excel in front of me so made a mistake

Red is everything greater smaller than todays date

Amber is everything between today and today + 7

Green is everything that's greater than today + 7