Condtional Formatting - Date and Time within 15 minutes of each other

[eli_m]

Hi all,

I currently have this helper column:

It combines two columns together using this formula:

=IF([@[Planning Date]]="N/A","",
IF(ISFORMULA([@[Planning Time]]),"",
CONCATENATE(TEXT([@[Planning Date]],"dd/mm/yy")," ",TEXT([@[Planning Time]],"hh:mm am/pm"))))

I want to make it so that the cells show up RED if any of the rows are less than 30 minutes (29 minutes to be exact) from each other.

What would be the best way and formula for this in conditional formatting?

Thanks in advance!

 

[eli_m]

Ok I managed to solve it but for some reason when there is a duplicate time only one of them turn red:

or

How do I make both times turn red?

 

[Jimmypop]

Maybe apply the Highlight Duplicates CF to the range and just choose your own style of formatting...

 

[eli_m]

Maybe apply the Highlight Duplicates CF to the range and just choose your own style of formatting...

Ah that did the trick!

One last thing:

This turns red:

but the 02:40pm should also turn red as I want to show both times as clashing. how do I do that?

 

[eli_m]

Ah that did the trick!

One last thing:

This turns red:
View attachment 92286

but the 02:40pm should also turn red as I want to show both times as clashing. how do I do that?

Anyone able to help me out with this?

 

[Peter_SSs]

Now see: Conditional Formatting Date/Time within 30 minutes of each other