CODEWITHSUNDEEP
excelexcel-formula
Lylie
Lylie

Reputation: 16

Excel - If time between 2 cells

enter image description here

EXCEL - 1st row example; clocked IN at 08:20 - clocked OUT at 17:03

What I want to do is basically in 8:00-8:59 - if row 1 was between them times then output a 1, if not, then output a 0

Any help is greatly appreciated

Upvotes: 0

Views: 120

Answers (1)

DTS
DTS

Reputation: 423

Assuming 08:00-08:59 is in cell J1, then you can put this formula into J2:

=IF(AND($A2>=VALUE(LEFT(J$1,FIND("-",J$1)-1)),$A2<=VALUE(RIGHT(J$1,LEN(J$1)-FIND("-",J$1)))),1,0)

Storing the time parameters as text forces the use of the VALUE function. Shifting everything down a row and putting the start of the hour in row 1 and the end of the hour in row 2 so it looks like this:

                                                    08:00   09:00   10:00   11:00   12:00
Clock1  C1  Clock2  C2  Clock3  C3  Clock4  C4      08:59   09:59   10:59   11:59   12:59
08:20   I   17:03   X                                       
10:20   I   16:03   X                                       
08:00   I   18:00   O                                       
10:11   I   17:00   O

Would change the formula to:

=IF(AND($A3>=J$1,$A3<=J$2),1,0)

Upvotes: 1

Related Questions