Time Between 2 times?

[MarkJamesDavies]

Hi,

I need a way to work out if a time is between 2 times including the original time. Please see below for example;

[TABLE="width: 520"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sign out time[/TD]
[TD]sign in time[/TD]
[TD]1:00[/TD]
[TD]1:15[/TD]
[TD]1:30[/TD]
[TD]1:45[/TD]
[TD]2:00[/TD]
[TD]2:15[/TD]
[TD]2:30[/TD]
[TD]2:45[/TD]
[/TR]
[TR]
[TD]1:00[/TD]
[TD]2:15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so IF there is a sign out time at 1:00, C3 should populate with YES or FALSE if it doesn't.

I've tired to use OR, MIN, MAX, IF, AND....But I just can't get it right.

PLEASE can someone help, I've spent over 9 hours staring at my laptop today. I'd be happy to make a donation to the site if it can be solved. It's driving me nuts.

I just need to break down the duration into 15 minutes.

Thank you in advance!

M

 

[Nishant94]

Is this what you are looking for:

Unknown
	A	B	C	D	E	F	G	H	I	J
2	Sign out time	sign in time	01:00	01:15	01:30	01:45	02:00	02:15	02:30	02:45
3	01:00	02:15	Yes	Yes	Yes	Yes	Yes	Yes	FALSE	FALSE
4	01:30	02:30	FALSE	FALSE	Yes	Yes	Yes	Yes	Yes	FALSE
Sheet2

Cell Formulas
Range	Formula
C3	=IF((C$2>=$A3)*(C$2<=$B3),"Yes",FALSE)

 

[MarkJamesDavies]

Is this what you are looking for:

Unknown

	A	B	C	D	E	F	G	H	I	J
Sign out time	sign in time

<tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]01:00[/TD]
[TD="align: right"]01:15[/TD]
[TD="align: right"]01:30[/TD]
[TD="align: right"]01:45[/TD]
[TD="align: right"]02:00[/TD]
[TD="align: right"]02:15[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]02:45[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]02:15[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]FALSE[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C3[/TH]
[TD="align: left"]=IF((C$2>=$A3)*(C$2<=$B3),"Yes",FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This works great apart from the 12am hour - if I put the sign out time as 00:01 and have the time 00:00 in C1, it doesn't work for me. Am I doing something wrong here?

A	B	C	D	E	F	G	H	I	J
Sign out time	sign in time

<tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]00:00[/TD]
[TD="align: right"]00:15[/TD]
[TD="align: right"]00:30[/TD]
[TD="align: right"]00:45[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]01:15[/TD]
[TD="align: right"]01:30[/TD]
[TD="align: right"]01:45[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]00:01[/TD]
[TD="align: right"]02:15[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]

</tbody>

Thank you in advance!

M

 

[Nishant94]

Unknown
	A	B	C	D	E	F	G	H	I	J
2	Sign out time	sign in time	00:00	00:15	00:30	00:45	01:00	01:15	01:30	01:45
3	00:01	02:15	YES	YES	YES	YES	YES	YES	YES	YES
4	01:30	02:30	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	YES	YES
5	00:30	01:15	FALSE	FALSE	YES	YES	YES	YES	FALSE	FALSE
6	01:00	02:15	FALSE	FALSE	FALSE	FALSE	YES	YES	YES	YES
Sheet2

Cell Formulas
Range	Formula
C3	=IF((LOOKUP($A3,$C$2:$J$2)<=C$2)*(C$2<=$B3),"YES","FALSE")