time interval within 12hrs

[kelvin_9]

Hi All, how can i calculate below time interval which is less than 12hrs from 2 cells, is it possible to use in conditional formatting?

11:30 - 22:00

08:00 - 17:30

wrong

10:00 - 19:30

11:30 - 21:00

correct

thanks so much

 

[kelvin_9]

=IF(((RIGHT(B1,5)-LEFT(A1,5))*24)<12,"less than 12hr","")

i've tried this in C1, but it doesn't work in C2

 

[Fluff]

In what way doesn't it work?
Both sets of data in your op are less then 12hrs, so why is one "wrong" & the other "correct"?

 

[kelvin_9]

sorry for any confusing.
i want to avoid any 12hrs interval from today's end to next day's start, so that i need to setup a formula to calculate is there any violation between 2 days.
back to my example 1, 22:00 shift end to the next day 08:00 start which is wrong as my request 12hrs interval.
ps. example 2 is well done so far, 16hrs interval.

thanks so much, fluff

 

[Fluff]

Ok, how about
=IF(RIGHT(A1,5)+0>LEFT(B1,5)+0.5,"less than 12hr","")

 

[kelvin_9]

this is GREAT.
may i have 2 more further question?
- if one of the comparison time return from my database is 00:00 - 00:00, can it blank as well? (11:30 - 21:00 vs 00:00 - 00:00)
- if comparison within a week, there is no doubt on any question. however if this sunday comparing with last saturday may caused any incorrect?
to prevent any mistake, what if i use another formula on comparison name first and this if statement on next?

	SATURDAY			SUNDAY
BEE	09:15 - 18:45		BEE	09:15 - 18:45	=IF(RIGHT(B2,5)+0>LEFT(E2,5)+0.5,"less than 12hr","")
BRENDA	12:00 - 21:30		BILL (NEW STAFF)		#VALUE!
BRIAN			BRENDA	08:00 - 18:00	#VALUE!
			BRIAN	13:00 - 21:00	#VALUE!

thanks so much, fluff

 

[Fluff]

You can use this to get rid of the errors
=IFERROR(IF(RIGHT(B2,5)+0>LEFT(E2,5)+0.5,"less than 12hr",""),"")

The formula assumes that the second time is the day after the first, so Saturday to Sunday is not a problem, but Saturday to Monday might give the wrong result

 

[kelvin_9]

i tried "11:30 - 21:30 vs 00:00 - 00:00", comes with "less than 12hr", how can i leave it blank as well as others which is not 12hr interval?
if new staff comes, the formula seems not accurate which caused error?

thanks so much, fluff

 

[Fluff]

How about
=IFERROR(IF(OR(RIGHT(A1,5)+0=0,LEFT(B1,5)+0=0),"",IF(RIGHT(A1,5)+0>LEFT(B1,5)+0.5,"less than 12hr","")),"")

 

[kelvin_9]

How about
=IFERROR(IF(OR(RIGHT(A1,5)+0=0,LEFT(B1,5)+0=0),"",IF(RIGHT(A1,5)+0>LEFT(B1,5)+0.5,"less than 12hr","")),"")

works great, thanks fluff
how about enhance the accuracy when new staff comes and avoid any error?