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]

And then try what i said with the formula not CTRL-H

 

[Gesyca_is_joy]

Ok leave the cell as it is and tell us what

=LEN(B2)

produces

it produces 11., each field has a leading space. even if i trim the spaces out from the field, and then format as TIME it doesn't change the field value.

 

[Gesyca_is_joy]

it produces 11., each field has a leading space. even if i trim the spaces out from the field, and then format as TIME it doesn't change the field value.

Ok, so i Trimmed the leading space, then i used ctrl+h to replace "PM" with " PM" then i formatted to time and 24 hour, so that 2:51:08 PM then became 14:51:08 and made sure my "start" and "end times" were formatted the same way and it worked!!!

ok so i know i can use the ctrl+h to manually insert spaces in the AM and PM part of this. and I will TRIM each field to remove the leading spaces. I will create a new column with the formula to remove the leading space and insert a space in front of AM/PM. THEN i will write my IF then off that new value.

 

[Gesyca_is_joy]

Ok...well If i use trim then insert a space before AM/PM and format as TIME it still doesn't work. it only works if i manually click into the cell and delete the space....what am i doing wrong?

 

[Gesyca_is_joy]

Welp, I got it working by using the steps above to manually remove and add spaces where needed.....then found out this is in GMT and need to convert to CST to report out with (it's always something)

So, created a new column and used the formula: =B2+1-TIME(6,0,0) to convert to CST. rewrote my ORIGINAL formula to calculate whether working hours or off hours from from CST column not the original GMT column......and........they all came up false..... Why is this so hard??

 

[steve the fish]

You need to find out what that character is:

=CODE(LEFT(B2,1))

produces what?

 

[Gesyca_is_joy]

You need to find out what that character is:

=CODE(LEFT(B2,1))

produces what?

on Cell B2,11:58:05 PM, I return the value of 12:00:00 AM

on Cell C2: 5:58:05 PM, derived from the formula: =B2+1-TIME(6,0,0), I return the value of 12:00:00 AM as well

 

[steve the fish]

Format that to number. What is the number?

 

[Gesyca_is_joy]

Format that to number. What is the number?

the B2 formula returned a value of 48.00 and the C2 cell returned 49.00

 

[steve the fish]

So there is no leading space? You need to help me out here cause you are taking me round in circles. Based on the original data do nothing to the contents of the cell at all unless i say. No ctrl-h. No nothing. What does the formula:

=CODE(LEFT(B2,1))

produce?