Conditional Formatting Formula

[Excel-Access]

Hi community

Can someone kindly help me in writing a coupld of formulae including a conditional formatting one where I have highlighted in yellow please

Thanks

 

[Luke M]

Hi Excel-Access,

It looks like the inputs are the Date Due, and Date Received?
In E5, I would suggest a formula like this:
=IF(ISBLANK(D5),TODAY()-C5,D5-C5)

If user inputs when item was received, then calculate difference between dates. Otherwise, uses today's date.

For the formatting, please clarify.

If submitted on due date Green Flag or Green Traffic Light
If late by 3 days Amber Flag / Tarffic Light
If late by > 5 days Red Flag / Traffic Light

So, if X <= 0, we are green. If X > 5, we are red. I would then guess that yellow would be for 0 < X <=5, but your text indicates it has something to do with 3? Anyway, I plugged in an initial CF rule. You should be able to easily adjust if needed.

 

[Excel-Access]

Thanks Luke, I need this slightly modified please as it is not working for me

If Date Received < or = to Date Due it is green

If Date Due is > 1 to 3 days it is Amber

If Date Due is > 4 days it is Red

Could you kindly modify please??

 

[Luke M]

Like this then?

 

[Excel-Access]

Hi

Almost what I want. My fault for not mentioning this.

Is it possible to have the number of days overdue in a separate column and the traffic light in a separate column (next to it) please

Thanks

 

[r1c1]

Do this:

1. In F5 paste the formula =IF(ISBLANK(D5),TODAY()-C5,D5-C5)
2. Select E5, go to Home > Conditional Formatting > Manage rules
3. Edit the traffic light rule
4. Click on Show icon only.

Now you have both traffic light and the value in 2 separate cells.