Search code examples
excelif-statementcomparison

Payroll time - Need to compare punch time against schedule time

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")))"

one row of my spreadsheet

Solution

  • I recommend creating a separate table with all the shift IN and OUT times. You can then use XLOOKUP to lookup the applicable IN and OUT times rather than hardcoding them into formula. This will it make it much easier to update if at anytime in the future the shift times are changed.

    L2 =LET(IN,XLOOKUP(("*" & J2 & "*") & K2, Shifts[Day] & Shifts[Shift], Shifts[IN], "not found",2),IF(E2 <= IN, IN, E2))

    N2 =LET(OUT, XLOOKUP(("*" & J2 & "*") & K2, Shifts[Day] & Shifts[Shift], Shifts[OUT], "not found", 2), IF(G2 >= OUT, OUT, G2))

    Note: as of this writing LET function is only available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2021, and Excel 2021 for Mac

    LET is used to assign the IN/OUT value found using XLOOKUP to a variable to be used more than once in the calculation formula. If you do not have LET you can just double the XLOOKUP function in the formula

    e.g., for L2 =IF(E2 <= XLOOKUP(("*" & J2 & "*") & K2, Shifts[Day] & Shifts[Shift], Shifts[IN], "not found",2), XLOOKUP(("*" & J2 & "*") & K2, Shifts[Day] & Shifts[Shift], Shifts[IN], "not found",2), E2)

    excel example