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]

 

[steve the fish]

That would suggest your time in the table is text not a real time. Test it by formatting one to number. If it doesnt change its text not time.

 

[Gesyca_is_joy]

That would suggest your time in the table is text not a real time. Test it by formatting one to number. If it doesnt change its text not time.

I tested and it is text. I am not sure how to address this now to get the results I need? can i copy and paste special into a new sheet to get that value to time/number? I can't edit the rows individually as this is a repeat report and quite large.

 

[steve the fish]

You could convert them relatively simply:

=0+REPLACE(B2,LEN(B2)-1,0," ")

 

[Gesyca_is_joy]

You could convert them relatively simply:

=0+REPLACE(B2,LEN(B2)-1,0," ")

I get a #VALUE error?

 

[steve the fish]

Ok get rid of the 0+ from the front of the formula. What does it produce?

 

[Gesyca_is_joy]

Ok get rid of the 0+ from the front of the formula. What does it produce?

it produces the value with a space between the last number and the AM/PM

12:51:08PM = 12:51:08 PM

still doesn't allow my original formula to differentiate between working hours and off hours, and I cannot format the results to a TIME format like a 24hr HH-MM-SS format

 

[steve the fish]

What about:

=0+TRIM(REPLACE(A1,LEN(A1)-1,0," "))

 

[Gesyca_is_joy]

What about:

=0+TRIM(REPLACE(A1,LEN(A1)-1,0," "))

I tried that as well prior to asking; not with a formula but i used Ctrl+H to replace "Pm" with " PM" and then also to remove the AM and PM. it still didn't work and once you remove the AM and PM you have no way to tell if the time is afternoon or morning because its on a 12 hour clock not a 24 hour.

 

[steve the fish]

Ok leave the cell as it is and tell us what

=LEN(B2)

produces