Time difference (conditional format)

[trevolly]

HI all.

I have a form where three durations of minutes are entered for an engine run (cells I29, I30 and I31) and they are added up and converted in to hours and mins and placed in to "Total Engine run time" (cell D31) using a formula.

The engineer has to put in a start and finish time of the test (cells D29 and D30). Is there any way to compare the two times in cells D29 and D30 to the "total engine run time" in cell D31, and using conditional formatting highlight if there is a difference and a cell would change colour to highlight this error ( or an error box show?)

i;e the screenshot I've attached would be wrong.

The idea is to stop engineers just slapping in any durations and times for the tests - I need accuracy that the three engine run tests add up to the duration between the actual start time and actual finish time.

Thank you all
Trevor

 

[JamesCanale]

This is pretty close.

MrExcelPlayground23.xlsx
	A	B	C	D
1	Start	12:00	T1	16
2	End	13:30	T2	12
3	Time	1:13	T3	45
Sheet16

Cell Formulas
Range	Formula
B3	B3	=SUM(D1:D3)/24/60

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	Expression	=INT(60*24*B3)<>INT(60*24*(B2-B1))	text	NO

 

[trevolly]

Thank you - it works when I recreate your demo but when I apply it to my form the conditional formatting stays red no matter what Strange, Ill keep playing.

 

[JamesCanale]

The thing with the time difference is funny. Sometimes when you subtract, you might get a difference including .00001 or a .999999 - but it looks normal. Sorting that out is the key. I try to solve it with "INT" - but since they are all less than 1, some multiplication. Maybe it's better to check ABS(B3-(B2-B1))<1/24/60 or such.