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!

Oh I can do this! Give me a minute.

Thank you 🙏

I'm halfway there! I've got a DATEDIFF between today's date and the due date. That returns a figure. Then I did an IF on whether it was due or not (YES, NO) and now I'm trying to do a conditional format on that YES,NO field. You can hide the DATEDIFF and YES/NO columns.

This version of Excel that I have is a bit different to the one I used to use - pretty sure you could do it just on the conditional formatting option, then.

I've used conditional formatting to get it to get red, but I can't make the coming up in 7 days amber.

That's the problem I'm having. I know I used to be able to do it, because I would do it for students' grades. It's driving me nuts that it isn't allowing multiple options. I'll have another go!

Mine will go amber but stays amber, it doesn't turn back to red.

Maybe i need to set them up in the opposite order. All the pages I find with insructions have it the way i'be neem doing it though with the amber rule after.

Try this? I'm going to try it now. here

Like this

When i put that first formula in, I get #NAME?
Even when I change it to =IF(J4>I4,”Overdue”,”Ontime”)

I'm putting that in column K

This really shouldn't be so fucking hard. It should be built in ffs, this is a proper simple and common request/function ffs.

This is what i've got so far

This is what Chat GPT suggests:
To create conditional formatting in Excel for column J based on the due date in column I, you can follow these steps:

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:
6. =AND($I1-TODAY()>=0,$I1-TODAY()<=7)
7. This formula checks if the difference between the date in column I and the current date (TODAY) is between 0 and 7 days.
8. 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.
9. Click "OK" to close the "Format Cells" dialog box.
10. 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. To make the cells turn red when they become overdue, you can follow these additional steps:

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:

=$I1-TODAY()<0

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.
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 turn red if the due date has passed.

I have no idea why this doesn't apply when I type it in.

That's what I've been doing - it's not working.

You don't want the IF in the conditional formatting box: that's implicit in the fact that you're conditional formatting.

I have just checked it and it works!

I haven't got Excel in front of me but I'd suggest that the OP's problem is not having the rules in the right order.
So first of all you have
Date > hard deadline => Red
then
Date > soft deadline => Amber

Thank you. I've just deleted all the previous and now done this and there's still no amber.

I haven't typeed the dates in manually, its a formula in col J, but the red condition works.

It worked for me but it made dates longer than 7 days turn white. GPT has corrected this and returned a new version. Do you want me to post it?

What is the formula in J that calculates the date?

Where does the 42 in your formula come from?

The formula in J is =I4+42

I is the date a survey is done
J is the date, 6 weeks or 42 days later that the report is due in

I want J to turn amber 7 days before this date then red when it is overdue.

I have a cell, A2, that has =TODAY() in.

can't you use =Today()+7?

I don't know. That is just there to set todays date on the spreadsheet for col J to know if its overdue.