The working shifts for employees are 4 types i.e
6 am to 2pm
2pm to 10 pm
10 pm to 6am (next day)
9 am to 5 pm
I need a formulas, if 1st category employee's in time is 5:47 am and out
time is 2:21pm the final time be rounded to 6 am (in time) and 2 pm(out time).
example:
in time -----out time---hours worked
5:25 am---2.23 pm ---8.00 hours
5:25 am ---1.30 pm ---7.30 hours
5:14 am --- 1.29 pm --7.00 hours
6:05am --- 2:18 pm ---8.00 hours
6:06 am ---1:59 pm ---7.00 hours
1 hour before in time and upto 5 minutes after in time be rounded to
standard in time,more than 5 minutes late be rounded to next 1/2 an hour in
time and even 1 second before out time be rouded down to 1/2 an hour out
time.These rules have to be appicable to all shifts.