Conditional Formatting/Formula

[Arts]

Hi all

I have two dates, Due Date and Actual Dates of which I am doing a calculation of to get the number of days between the actual date and due date. What I was hoping to do was use conditional formatting to colour the cell different colours depending on how late or early the date difference was. Now the issue I seem to have is that I am trying to highlight anything that is over a certain time by extracting just the number from the Date Difference Column.
so anything > refers to Days Late, < Days Early

Red: >28 <-28 days (so anything more than -28 days Early as Red)
Amber: >14 but less than 28, <-28 (so anything upto -28 days Early as Amber)
Green: <14 <-14 (so anything upto -14 days Early as Green)

Due Date	Actual End Date	Date Difference
30/08/23​	02/09/23​	3 Days late
14/07/22​	21/04/22​	-84 Days Early
14/04/23​	20/03/23​	-25 Days Early
14/04/23​	20/03/23​	-25 Days Early

Within the conditional formatting, I have done (shown below) this seems to highlight the 3 days late as Red but should be Green. I maybe completely on the wrong path and there could be an easier way to extract the number from the text within conditional formatting, as always any help is appreciated.

 

[Joe4]

You will need three rules, one for each color.

Red formula:

=ABS(LEFT(M11,SEARCH" ",M11-1)*1)=>28

Amber formula:

=AND(ABS(LEFT(M11,SEARCH" ",M11-1)*1)>14,ABS(LEFT(M11,SEARCH" ",M11-1)*1)<28)

Green formula:

=ABS(LEFT(M11,SEARCH" ",M11-1)*1)<=14

Note: that you may need to edit the formula slightly depending on what color you want EXACTLY 14 and 28 days to me (i.e. change ">" to ">=' and/or "<" to "<=" in the appropriate rules).

 

[Arts]

You will need three rules, one for each color.

Red formula:

=ABS(LEFT(M11,SEARCH" ",M11-1)*1)=>28

Amber formula:

=AND(ABS(LEFT(M11,SEARCH" ",M11-1)*1)>14,ABS(LEFT(M11,SEARCH" ",M11-1)*1)<28)

Green formula:

=ABS(LEFT(M11,SEARCH" ",M11-1)*1)<=14

Note: that you may need to edit the formula slightly depending on what color you want EXACTLY 14 and 28 days to me (i.e. change ">" to ">=' and/or "<" to "<=" in the appropriate rules).

Thank you so much for this Joe did exactly what I needed! (apologies for the delayed response people seem to be working on a Friday today and it seems to all be coming my way)

I started with the RED condition first and it didn't seem to want to accept it but then switched the "=>28" to ">=28" which seemed to do the trick!

It doesn't allow me to mark two responses as a solution so apologies if this may affect your status amongst the MVP elites, but on your say so happy to remove the first solution and mark it against yours....

 

[Joe4]

It doesn't allow me to mark two responses as a solution so apologies if this may affect your status amongst the MVP elites, but on your say so happy to remove the first solution and mark it against yours....

No worries. It doesn't affect anything, other than counts (which are interesting, but not really used for anything).

Glad we were able to help!