I need help working on a formula that will automate the time in/out corrections of clock in/outs of several employees working different shifts and days.
I have 6 shifts (6:00am - 2:30pm, 2:00pm - 10:30pm, 10:00pm - 5:30am Mon-Fr. and on Sundays I have 7:00am - 3:30pm, 2:00pm - 10:30pm, 10:00pm - 3:00am)
- Employees do not punch on time all the time.
- I need to check each punch based on the schedule above (in/out) and change the punch to the scheduled punch in/out time
- If employee punched in late, leave the punch time alone (as he/she is late).
- The same with the punch out, if employee punched out after the scheduled punch out time, I need to change the punch out time to the scheduled "out" time
- If punched out before the scheduled punch out time, the punch is left alone.
So far to make things a bit less complicated, I assigned each employee an ID showing the shift the employee is working for ex: 011001 ->01=department, 1=schedule(morning 6:00am-2:30pm), 001-employee number
Problems:
- The night employees (10:00pm - 5:30am) work on Sunday as well (10:00pm - 3:00 am)
I was working with the following formula, but I do not see the error with the "if" and time comparison.
"=IF(K197="013", IF(Table1[@[IN TIME]]>="10:00 PM", "10:00 PM", Table1[@[IN TIME]]),IF(K197="012", IF(Table1[@[IN TIME]]>="2:00 PM", "2:00 PM", Table1[@[IN TIME]]), IF(K197="011",IF(Table1[@[IN TIME]]<="6:00 AM", "6:00 AM",Table1[@[IN TIME]]), "Err")))"
CodePudding user response：
I recommend creating a separate table with all the shift IN and OUT times. You can then use 