Attendance Workout

[mistryat]

Hi all
request your help to calculate the following in excel.

Office timing is 09:30
if report late by 5 min = in time
if report late by 06 to 30 min = half hour late
if report late by 30 to 1 hr = 1 hr late
if late mark count more than 4 times deduct half day salary

[M E Good @ XL4M]

Hello
Not sure how you intend to use this answer but I hope it helps

A2 is name of person, B2 to H2 enter sign in times

B3 =IF(B2<=TIME(9,35,0),"",IF(B2<TIME(10,0,0),TIME(0,30,0),IF(B2<TIME(10,30,0),TIME(1,0,0),B2-TIME(9,30,0))))
Copy across to H3
I3 =IF(COUNT(B2:H2)>4,"Half day salary","")

The formulas in row 3 can be moved to row 2 next to the day sign ins if more convenient.