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.

 

[C Moore]

try multiplying by 1 to take it from text to number.
=LEFT(M11,SEARCH" ",M11-1)*1>28

 

[Arts]

That has done it!! Thank you so much!

Can I ask what is the text part? That it's giving me true or the date is seen as text? It was only yesterday where I I had a date question and Joe mentioned that dates are numbers where I needed to add 14 days to a date so am unsure where the Text aspect is

Excel Date Query/Confirmation

Hi all I have a list of due dates and actual end dates as listed below, I have been told that if the actual end date is within two weeks of the due date then this can be seen as "within time". The Due Date is attained from a report and is sometimes blank so I have used the formula...

www.mrexcel.com

edit if I use the formula =LEFT(M11,SEARCH" ",M11-1) this gives me a number

 

[C Moore]

That has done it!! Thank you so much!

Can I ask what is the text part? That it's giving me true or the date is seen as text? It was only yesterday where I I had a date question and Joe mentioned that dates are numbers where I needed to add 14 days to a date so am unsure where the Text aspect is

Excel Date Query/Confirmation

Hi all I have a list of due dates and actual end dates as listed below, I have been told that if the actual end date is within two weeks of the due date then this can be seen as "within time". The Due Date is attained from a report and is sometimes blank so I have used the formula...

www.mrexcel.com

edit if I use the formula =LEFT(M11,SEARCH" ",M11-1) this gives me a number

It looks like a number, but does it show up on the left side of the column? Means its recognized as text. Left, right, and mid are text functions.

 

[dreid1011]

That has done it!! Thank you so much!

Can I ask what is the text part? That it's giving me true or the date is seen as text? It was only yesterday where I I had a date question and Joe mentioned that dates are numbers where I needed to add 14 days to a date so am unsure where the Text aspect is

Excel Date Query/Confirmation

Hi all I have a list of due dates and actual end dates as listed below, I have been told that if the actual end date is within two weeks of the due date then this can be seen as "within time". The Due Date is attained from a report and is sometimes blank so I have used the formula...

www.mrexcel.com

edit if I use the formula =LEFT(M11,SEARCH" ",M11-1) this gives me a number

The result of the LEFT function by default is a string (Text). Multiplying by 1 converts the text to a number.

 

[Arts]

It looks like a number, but does it show up on the left side of the column? Means its recognized as text. Left, right, and mid are text functions.

I'll check tomorrow out of all things we had a fire alarm in the office and subsequently left for the day. I'll confirm tomorrow about it being on the left though suspect you are right.

 

[Arts]

The result of the LEFT function by default is a string (Text). Multiplying by 1 converts the text to a number.

Thanks for this dreid, helps explains it!

 

[Arts]

It looks like a number, but does it show up on the left side of the column? Means its recognized as text. Left, right, and mid are text functions.

It does indeed show up on the left, good call.

How would I then alter the formula to include a range I seem to struggling to get there so if I wanted it highlight red if it was over 28 days late and then highlight red if it was more than -28 days early

Edit: Or would it be a list of single formula entries within the conditional formatting

 

[Joe4]

It does indeed show up on the left, good call.

How would I then alter the formula to include a range I seem to struggling to get there so if I wanted it highlight red if it was over 28 days late and then highlight red if it was more than -28 days early

Edit: Or would it be a list of single formula entries within the conditional formatting

If you use the ABS function (absolute value), it will convert all negative numbers to positive, so you could do something like:

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

 

[Arts]

If you use the ABS function (absolute value), it will convert all negative numbers to positive, so you could do something like:

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

Ah Joe (Afternoon) I was literally tussling with this as you replied as the issue I was having was if it the result was less than 14 days I was hoping this would go green but then I had -111 days which should be red is turning Green not to mention having to incorporate the ambers... I shall give your reply a whirl