Calculating hours of late

[jazie07]

Looking for assistance in calculating how many hours late.

A	B	C
IN	OUT	LATE
8:16 AM	5:00 AM	L1

<tbody>
</tbody>

In C, I would like the remarks L1 if late between 8:16 AM to 9:00AM, L2 if 9:01 AM to 10:00AM, L3 if 10:01 AM to 11AM and so on.

I would like any help so each time I will make my DTR report, it will automatically place the remarks (L1 or L or L3....) if the database of time log are pasted on my worksheet.

It will at least shorten my time doing the daily time record of 50 employees.

Your help would be greatly appreciated.

Thank you very much

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​
1​	IN	OUT	LATE		8:00 AM​	L1
2​	8:16 AM​	5:00 AM​	L1		9:00 AM​	L2
3​	11:16 AM​	5:00 AM​	L4		10:00 AM​	L3
4​	10:16 AM​	5:00 AM​	L3		11:00 AM​	L4
5​	12:16 PM​	5:00 AM​	L5		12:00 PM​	L5
6​					1:00 PM​	L6

Sheet: Sheet80

Formula in cell C2:
=LOOKUP(A2,$E$1:$E$6,$F$1:$F$6)

 

[jazie07]

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	IN	OUT	LATE		8:00 AM​	L1
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	8:16 AM​	5:00 AM​	L1		9:00 AM​	L2
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	11:16 AM​	5:00 AM​	L4		10:00 AM​	L3
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	10:16 AM​	5:00 AM​	L3		11:00 AM​	L4
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	12:16 PM​	5:00 AM​	L5		12:00 PM​	L5
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​					1:00 PM​	L6

<tbody>
</tbody>

Sheet: Sheet80

<tbody>
</tbody>

Formula in cell C2:
=LOOKUP(A2,$E$1:$E$6,$F$1:$F$6)

Thank you so much sir. But one more, I forgot to mention earlier. How about in the afternoon where 1:01 PM will give same outcome of L1, 2PM = L2, and so on but I want them to have an argument in the same cell(C). is it possible to have that statement?

if 8:16AM = L1 and 1:16PM = L1

Thanks again

 

[Oscar Cronquist]

The formula seems to be working for that case as well, here is an example:

	A​	B​	C​	D​	E​	F​
1​	IN	OUT	LATE		8:00 AM​	L1
2​	8:16 AM​	5:00 AM​	L1		9:00 AM​	L2
3​	11:16 AM​	5:00 AM​	L4		10:00 AM​	L3
4​	4:44 PM​	5:00 AM​	L4		11:00 AM​	L4
5​	12:16 PM​	5:00 AM​	L5		12:00 PM​	L5
6​					1:00 PM​	L1
7​					2:00 PM​	L2
8​					3:00 PM​	L3
9​					4:00 PM​	L4
10​					5:00 PM​	L5

Sheet: Sheet80

 

[jazie07]

The formula seems to be working for that case as well, here is an example:

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	IN	OUT	LATE		8:00 AM​	L1
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	8:16 AM​	5:00 AM​	L1		9:00 AM​	L2
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	11:16 AM​	5:00 AM​	L4		10:00 AM​	L3
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	4:44 PM​	5:00 AM​	L4		11:00 AM​	L4
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	12:16 PM​	5:00 AM​	L5		12:00 PM​	L5
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​					1:00 PM​	L1
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​					2:00 PM​	L2
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​					3:00 PM​	L3
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​					4:00 PM​	L4
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​					5:00 PM​	L5

<tbody>
</tbody>

Sheet: Sheet80

<tbody>
</tbody>

I did that sir and just changed the value without altering the formula. Now it work. Thank you so much!!!