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;

<tbody>
</tbody>

Sign out time	sign in time	1:00	1:15	1:30	1:45	2:00	2:15	2:30	2:45
1:00	2:15

<tbody>
</tbody>

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
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

<tbody>
</tbody>

Sheet2​

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

<tbody>
</tbody>

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
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	FALSE	Yes	Yes	Yes	Yes	Yes	Yes	Yes
4	01:30	02:30	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	Yes	Yes

<tbody>
</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")