Conditional Format True and Wrong Calculation

[Emre06]

Greetings. I need help with the conditional format of calculations. Time formats are in 24 hours (hh:mm). How can I conditional format wrong calculations (Third row calculated wrongly) and true calculations (1,2,4th row true). Thank you for your time. ( For an example just wanted to 3rd column [Duration] Green if correct, Red if wrong calculation)

Kitap1
	A	B	C
1	Departure	Arrival	Duration
2	11:00	13:00	02:00
3	23:10	01:00	01:50
4	10:50	12:50	03:00
5	08:00	10:00	02:00
Sayfa1

 

[BigBeachBananas]

How are you checking if it's correct or not?

 

[Emre06]

I don't have a different column for calculation. I tried this code but its not working for all "Conditional format" C:C -> Equal ->

=(B:B-A:A)

 

[duggie33]

I think your formatting is the main issue. In row 3, you cannot subtract A3 from B3 and get a number that can be formatted as a time because it is a negative number. The A3 and B3 values must be a date and time for the difference to be calculated correctly. B3 would be the next day.

Book1
	A	B	C
7	Departure	Arrival	Duration
8	11:00	13:00	2:00
9	23:10	1:00	1:50
10	10:50	12:50	2:00
11	8:00	10:00	2:00
Sheet1

Cell Formulas
Range	Formula
C8:C11	C8	=B8-A8

Hope that helps,

Doug

 

[Emre06]

All three rows recieving information from other sources, manually entering all the rows is not practical.
Any other solutions?

 

[duggie33]

Could you put a formula in your duration column that correctly calculates the duration? Something like this...

=IF(A2<B2,B2-A2,24-A2+B2)

Hope that helps,

Doug

 

[Emre06]

Could you put a formula in your duration column that correctly calculates the duration? Something like this...

=IF(A2<B2,B2-A2,24-A2+B2)

Hope that helps,

Doug

Thank you it works. But data to 3rd column automatically added from Microsoft forms. it will erase formula, because of it I am trying to verify calculations without touching column and row.

 

[BigBeachBananas]

An issue with using the formula is the rounding. On the surface, they look the same...

Book1.xlsx
	A	B	C	D	E
1	Departure	Arrival	Duration	Calculated Duration	Check
2	11:00	13:00	2:00	2:00	FALSE
3	23:10	1:00	1:50	1:50	FALSE
4	10:50	12:50	3:00	2:00	FALSE
5	8:00	10:00	2:00	2:00	FALSE
Sheet3

Cell Formulas
Range	Formula
D2:D5	D2	=IF(B2<A2, B2+1-A2, B2-A2)
E2:E5	E2	=D2=C2

But they're in fact different.

Book1.xlsx
	A	B	C	D	E
1	Departure	Arrival	Duration	Calculated Duration	Check
2	11:00	13:00	0.0833333333333333000	0.0833333333333340000	FALSE
3	23:10	1:00	0.0763888888888889000	0.0763888888888887000	FALSE
4	10:50	12:50	0.1250000000000000000	0.0833333333333330000	FALSE
5	8:00	10:00	0.0833333333333333000	0.0833333333333340000	FALSE
Sheet3

Cell Formulas
Range	Formula
D2:D5	D2	=IF(B2<A2, B2+1-A2, B2-A2)
E2:E5	E2	=D2=C2

 

[Emre06]

@BigBeachBananas Thank you very much for the detailed examination. Do you have any recommendations on how we can solve this?

 

[BigBeachBananas]

Maybe round both to 10 decimal places or however many you feel comfortable.

Book1.xlsx
	A	B	C
1	Departure	Arrival	Duration
2	11:00	13:00	2:00
3	23:10	1:00	1:50
4	10:50	12:50	3:00
5	8:00	10:00	2:00
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C5	Expression	=ROUND(MOD(B2-A2,1),10)<>ROUND(C2,10)	text	NO
C2:C5	Expression	=ROUND(MOD(B2-A2,1),10)=ROUND(C2,10)	text	NO