Two dates, two cells - How do I format the second cell to change colour after x amount of time has elapsed from the first date?

[Kikaiki]

So, I have a workbook of jobs that have 'Job start' and 'Job Finish' values as part of it - For this example, lets say 'Job Start' is column C, and 'Job End' is column D - Both have Date and Time in each cell.

I need the cells in column D to turn red if the data is over 2 days from that in column C

For example:

22/11/2024 09:16

25/11/2024 08:00

The time between these is more than 2 days so I need the cell on the right to turn red

I've looked around but I can't seem to fina any formulas that do specifically this, they are more aimed at turning the same cell red if elapses=

 

[Sergius]

Hello! Is this what you need?

TRT.xlsx
	C	D
1	22.11.2024 9:16	25.11.2024 8:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Expression	=C1+2	text	NO

 

[Kikaiki]

Hello! Is this what you need?

TRT.xlsx
	C	D
1	22.11.2024 9:16	25.11.2024 8:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Expression	=C1+2	text	NO

So, yes ... but - I can't seem to put it into play?

I don't know whether it's because im used to old excel conditional formatting where you had to type out the full formula, but i just can't seem to get an formula for this to work in 365

I'm not sure if i'm just have a very dim moment and missing something very simple, or i'm doing it entirely wrong.

I thought i had it working for a second, then went to apply it to the additional rows and they all just turned red regardless.

If you could step by step it for me i would appreciate it - It appears my excel skills are rustier than I would have liked to admit :/

 

[Sergius]

Imagine a fragment of your data.

 

[Kikaiki]

So from this : Rows 1-3 I would expect to turn red as they exceeded 2 days, but the next three wouldn't as they are within the limit i am trying to set

I am basically looking to CD column D to react to Column C but doing a terrible job of it **sigh**1

 

[Cubist]

Try:

Book1
	C	D
1	11/22/24 9:16	11/25/24 8:00
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Expression	=(D1-C1)>2	text	NO

 

[Sergius]

TRT.xlsx
	C	D
1	15.11.2024 12:36	27.11.2024 8:00
2	18.11.2024 18:21	22.11.2024 12:00
3	22.11.2024 9:16	25.11.2024 8:00
4	25.11.2024 15:29	26.11.2024 13:00
5	24.11.2024 13:04	25.11.2024 8:00
6	19.11.2024 16:34	23.11.2024 10:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1:D6	Expression	=DATEDIF(C1; D1; "d")>2	text	NO

 

[Kikaiki]

TRT.xlsx
	C	D
1	15.11.2024 12:36	27.11.2024 8:00
2	18.11.2024 18:21	22.11.2024 12:00
3	22.11.2024 9:16	25.11.2024 8:00
4	25.11.2024 15:29	26.11.2024 13:00
5	24.11.2024 13:04	25.11.2024 8:00
6	19.11.2024 16:34	23.11.2024 10:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1:D6	Expression	=DATEDIF(C1; D1; "d")>2	text	NO

So, I copy and pasted:

And got this:

 

[Kikaiki]

Try:

Book1
	C	D
1	11/22/24 9:16	11/25/24 8:00
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Expression	=(D1-C1)>2	text	NO

We have a winner!! Thankyou!!

I'm kicking myself for it being so simple but at least we got there