Time Difference > 1 Min --> Cell Formatting

[30percent]

I have 19:30 in cell C7 and 19:31 in D7.
I have a formula in E7 cell where = C7-D7.
When I have cell E7 format as number it would display the result as -0.00069.
However, if I have cell format as 13:30:55, it would display result as #######.

What I'm trying to do is that, if the time discrepancies between cell C7 and D7 is more than 1 minute,
I would like to condition the cell to have its color as red so as to alert me of the discrepancy.

How do I do that? Thanks

 

[bebo021999]

use this CF formula:

=ABS(C7-D7)>1/1440

 

[Alex Blakenburg]

You can't display a negative value using the Date/Time format.
Is column C always going to be less than column D ? If it is then reverse your subtraction.
bebo has addressed the conditional formatting aspect but that won't fix how you display the negative time.

 

[30percent]

You can't display a negative value using the Date/Time format.
Is column C always going to be less than column D ? If it is then reverse your subtraction.
bebo has addressed the conditional formatting aspect but that won't fix how you display the negative time.

It is. I reversed the subtration: =ABS(D7-C7)>TIME(0,1,0).
It works except for one cell where I have time as 17:51 in cell D8 and 17:50 in cell C8; =ABS(D8-C8) would display 0:01:00.
=ABS(D8-C8)>TIME(0,1,0) ---> True (as the difference between 17:51 and 17:50 = 1 min, it should not be true)

note: cell D8 would vlookup and reference a cell with a formula as such: =IF(GMT!C14<5/24,1,0)+GMT!C14-5 (could this be the cause of issue?).

thanks

 

[30percent]

use this CF formula:

=ABS(C7-D7)>1/1440

I tried this. Many that I expect to be True would display as False.
However, if I tweaked it to =ABS(C7-D7)>Time(0,01,00) it would work except for one cell that I expect it to be FALSE.

 

[Alex Blakenburg]

Time is a bit painful since 1 second as a decimal is not an exact number as a fraction, so introducing a calculation like you have in column D will introduce that issue.
See if modifying your formula to the below gives you consistent results.

=ROUND(ABS(D8-C8)-TIME(0,1,0),5)>0

 

[30percent]

Time is a bit painful since 1 second as a decimal is not an exact number as a fraction, so introducing a calculation like you have in column D will introduce that issue.
See if modifying your formula to the below gives you consistent results.

=ROUND(ABS(D8-C8)-TIME(0,1,0),5)>0

I still get the same result: TRUE. I changed the parameter 5 and varies it from 1 to 10, each numbers gives the same result.

=ABS(D8-C8) --> 0.000694
=TIME(0,1,0) ---> 12:01am
the difference between the formula above is 12:00am.

I think I'm overcomplicating things. I want the cell to alert or set to true if the difference between cell D8 and C8 is more than 1 minute. Is there an easy way to do it?

 

[Alex Blakenburg]

=ABS(D8-C8) --> 0.000694
=TIME(0,1,0) ---> 12:01am
the difference between the formula above is 12:00am.

This is not how it works, see the formula evaluate images below the 12:01 converts to a decimal equivalent of 1 minute.

You need to give us an example of when the formula doesn't work.
Preferably give us an XL2BB of the actual data.
If not convert the cells to General format and give us the exact decimal values you have in the 2 cells when it doesn't work.