Gesyca_is_joy
Board Regular
- Joined
- Apr 24, 2014
- Messages
- 90
- Office Version
- 365
- Platform
- Windows
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
<colgroup><col><col><col></colgroup><tbody>
</tbody>
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
Date | Time | Work Hours |
7/10/2018 | 12:51:08AM | Off Hours |
7/10/2018 | 5:47:06AM | Off Hours |
7/10/2018 | 6:22:05AM | Off Hours |
7/10/2018 | 8:01:06AM | Off Hours |
7/10/2018 | 12:55:53PM | Off Hours |
7/10/2018 | 4:52:20PM | Off Hours |
7/10/2018 | 5:51:56PM | Off Hours |
7/10/2018 | 6:50:08PM | Off Hours |
7/10/2018 | 8:48:20PM | Off Hours |
7/10/2018 | 11:47:05PM | Off Hours |
7/10/2018 | 12:45:53PM | Off Hours |
<colgroup><col><col><col></colgroup><tbody>
</tbody>