Write and IF THEN if cell is between two times

[Gesyca_is_joy]

I receive a large file each month which tracks staff who badge through a particular badge reader. I am trying to write a formula to tell me if each row was captured during regular business hours or not. below you see a paste of my excerpt (I cannot attach a sample excel file to this for some reason). I keep getting a response in Column C of FALSE, or "Off Hours" no matter the time. I think it has to do with the fact that i get this as unformatted data and cannot format column B to a time format (wanted 24hr) but i'm dealing with upwards of 60k rows per report so i can't manually make that column a time format line by line.

my Formula is: =IF(AND(B2>$H$1,B2<$H$2),"Yes","Off Hours")

H1 = 06:00:00 AM
H2 = 07:00:00 PM

A B C
[TABLE="width: 243"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Work Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 12:51:08AM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 5:47:06AM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 6:22:05AM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 8:01:06AM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 12:55:53PM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 4:52:20PM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 5:51:56PM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 6:50:08PM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 8:48:20PM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 11:47:05PM[/TD]
[TD]Off Hours[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2018[/TD]
[TD] 12:45:53PM[/TD]
[TD]Off Hours[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gesyca_is_joy]

Apologies. ok. to recap.

the original value in Cell B2 is a time stamp; which i receive as an export from a database. the end goal is to be able to use a formula to tell whether that time stamp occurred during working hours.

Cell B2 is the original timestamp (in GMT)
Cell C2 is the timestamp in CST =B2+1-TIME(6,0,0)
Cell E2 is the formula defining whether or not it happened in working hours. =IF(B2="","",IF(AND(B2>Lists!$B$2,B2<Lists!$B$3),"Working Hours","Off Hours"))

Currently Cell B2 is functional, it works when formatted as time (leading space issue is solved.) and Cell E2, as it is now, functions. What happens is that if I redirect the formula in E2 to reference C2 instead of B2 then the formula in E2 no longer returns a correct value.

 

[steve the fish]

Anything greater than 1 is never going to satisfy your time formulas. Time on its own is a value between 0 and 1. So the question is why are you adding 1? If you need to convert the time for time zone purposes then you will also need to convert it back to a value between 0 and 1 so maybe try:

=MOD(B2-TIME(6,0,0),1)

 

[Gesyca_is_joy]

Anything greater than 1 is never going to satisfy your time formulas. Time on its own is a value between 0 and 1. So the question is why are you adding 1? If you need to convert the time for time zone purposes then you will also need to convert it back to a value between 0 and 1 so maybe try:

=MOD(B2-TIME(6,0,0),1)

That fixed it Thank you!! All the formulas work appropriately now! I appreciate all the assistance, there is no way I could have made this report useful otherwise.