{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["885.5","885.5 (an)"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"885.5","index":0,"n_rows":26,"n_cols":7,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
["Excel Magic Trick 885: QUOTIENT & MOD Calculate Payroll Penalty For Every 15 Minute Late"]
["Excel Magic Trick 885.5: Part 2 Calculate Payroll Penalty QUOTIENT & OR"]
["15 minutes or less = NOT late. Late = More Than 15."]
["Time Increment","Dollar Penalty","Required Time"]
["00:15:00",2,"08:00:00"]
["TimeIn","Penalty"]
["08:14:00",null,null,"Ctrl + Shift + ~","Apply General Number Format",null,"Apply General Number Format"]
["08:15:00"]
["08:16:00",null,null,null,5]
["08:30:00",null,null,null,2]
["08:31:00",null,null,null,"QUOTIENT",{"$f":"QUOTIENT(E9,E10)","v":2}]
["08:45:00",null,null,null,"MOD",{"$f":"MOD(E9,E10)","v":1}]
["09:00:00"]
["09:57:00"]
["11:08:00"]
[]
["IF","QUOTIENT 1","QUOTIENT 2","INT 1","INT 2"]
[{"$f":"IF(MOD(MINUTE(A7),15),QUOTIENT(A7-$C$5,$A$5)*$B$5,QUOTIENT(A7-$C$5,$A$5)*$B$5-$B$5)","v":0},{"$f":"(QUOTIENT(A7-$C$5,$A$5)-(MOD(MINUTE(A7),$A$5*1440)=0))*$B$5","v":0},{"$f":"QUOTIENT(A7-$C$5,$A$5)*$B$5-(MOD(MINUTE(A7),15)=0)*$B$5","v":0},{"$f":"INT((A7-$C$5)/\"00:15\")*$B$5-(MOD(MINUTE(A7),15)=0)*$B$5","v":0},{"$f":"(INT((A7-$C$5)/\"00:15\")-(MOD(MINUTE(A7),15)=0))*$B$5","v":0}]
[{"$f":"IF(MOD(MINUTE(A8),15),QUOTIENT(A8-$C$5,$A$5)*$B$5,QUOTIENT(A8-$C$5,$A$5)*$B$5-$B$5)","v":0},{"$f":"(QUOTIENT(A8-$C$5,$A$5)-(MOD(MINUTE(A8),$A$5*1440)=0))*$B$5","v":0},{"$f":"QUOTIENT(A8-$C$5,$A$5)*$B$5-(MOD(MINUTE(A8),$A$5*1440)=0)*$B$5","v":0},{"$f":"INT((A8-$C$5)/\"00:15\")*$B$5-(MOD(MINUTE(A8),15)=0)*$B$5","v":0},0]
[2,{"$f":"(QUOTIENT(A9-$C$5,$A$5)-(MOD(MINUTE(A9),$A$5*1440)=0))*$B$5","v":2},{"$f":"QUOTIENT(A9-$C$5,$A$5)*$B$5-(MOD(MINUTE(A9),$A$5*1440)=0)*$B$5","v":2},2,2]
[2,{"$f":"(QUOTIENT(A10-$C$5,$A$5)-(MOD(MINUTE(A10),$A$5*1440)=0))*$B$5","v":2},{"$f":"QUOTIENT(A10-$C$5,$A$5)*$B$5-(MOD(MINUTE(A10),$A$5*1440)=0)*$B$5","v":2},2,2]
[4,{"$f":"(QUOTIENT(A11-$C$5,$A$5)-(MOD(MINUTE(A11),$A$5*1440)=0))*$B$5","v":4},{"$f":"QUOTIENT(A11-$C$5,$A$5)*$B$5-(MOD(MINUTE(A11),$A$5*1440)=0)*$B$5","v":4},4,4]
[4,{"$f":"(QUOTIENT(A12-$C$5,$A$5)-(MOD(MINUTE(A12),$A$5*1440)=0))*$B$5","v":4},{"$f":"QUOTIENT(A12-$C$5,$A$5)*$B$5-(MOD(MINUTE(A12),$A$5*1440)=0)*$B$5","v":4},4,4]
[6,{"$f":"(QUOTIENT(A13-$C$5,$A$5)-(MOD(MINUTE(A13),$A$5*1440)=0))*$B$5","v":6},{"$f":"QUOTIENT(A13-$C$5,$A$5)*$B$5-(MOD(MINUTE(A13),$A$5*1440)=0)*$B$5","v":6},6,6]
[14,{"$f":"(QUOTIENT(A14-$C$5,$A$5)-(MOD(MINUTE(A14),$A$5*1440)=0))*$B$5","v":14},{"$f":"QUOTIENT(A14-$C$5,$A$5)*$B$5-(MOD(MINUTE(A14),$A$5*1440)=0)*$B$5","v":14},14,14]
[24,{"$f":"(QUOTIENT(A15-$C$5,$A$5)-(MOD(MINUTE(A15),$A$5*1440)=0))*$B$5","v":24},{"$f":"QUOTIENT(A15-$C$5,$A$5)*$B$5-(MOD(MINUTE(A15),$A$5*1440)=0)*$B$5","v":24},24,24]

{"sheet":"885.5 (an)","index":1,"n_rows":26,"n_cols":7,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
["Excel Magic Trick 885: QUOTIENT & MOD Calculate Payroll Penalty For Every 15 Minute Late"]
["Excel Magic Trick 885.5: Part 2 Calculate Payroll Penalty QUOTIENT & OR"]
["15 minutes or less = NOT late. Late = More Than 15."]
["Time Increment","Dollar Penalty","Required Time"]
["00:15:00",2,"08:00:00"]
["TimeIn","Penalty"]
["08:14:00",{"$f":"(QUOTIENT(A7-$C$5,$A$5)-OR(MINUTE(A7)={0,15,30,45}))*$B$5","v":0},null,"Ctrl + Shift + ~","Apply General Number Format",null,"Apply General Number Format"]
["08:15:00",{"$f":"(QUOTIENT(A8-$C$5,$A$5)-OR(MINUTE(A8)={0,15,30,45}))*$B$5","v":0}]
["08:16:00",{"$f":"(QUOTIENT(A9-$C$5,$A$5)-OR(MINUTE(A9)={0,15,30,45}))*$B$5","v":2},null,null,5]
["08:30:00",{"$f":"(QUOTIENT(A10-$C$5,$A$5)-OR(MINUTE(A10)={0,15,30,45}))*$B$5","v":2},null,null,2]
["08:31:00",{"$f":"(QUOTIENT(A11-$C$5,$A$5)-OR(MINUTE(A11)={0,15,30,45}))*$B$5","v":4},null,null,"QUOTIENT",{"$f":"QUOTIENT(E9,E10)","v":2}]
["08:45:00",{"$f":"(QUOTIENT(A12-$C$5,$A$5)-OR(MINUTE(A12)={0,15,30,45}))*$B$5","v":4},null,null,"MOD",{"$f":"MOD(E9,E10)","v":1}]
["09:00:00",{"$f":"(QUOTIENT(A13-$C$5,$A$5)-OR(MINUTE(A13)={0,15,30,45}))*$B$5","v":6}]
["09:57:00",{"$f":"(QUOTIENT(A14-$C$5,$A$5)-OR(MINUTE(A14)={0,15,30,45}))*$B$5","v":14}]
["11:08:00",{"$f":"(QUOTIENT(A15-$C$5,$A$5)-OR(MINUTE(A15)={0,15,30,45}))*$B$5","v":24}]
[]
["IF","QUOTIENT 1","QUOTIENT 2","INT 1","INT 2"]
[{"$f":"IF(MOD(MINUTE(A7),15),QUOTIENT(A7-$C$5,$A$5)*$B$5,QUOTIENT(A7-$C$5,$A$5)*$B$5-$B$5)","v":0},{"$f":"(QUOTIENT(A7-$C$5,$A$5)-(MOD(MINUTE(A7),$A$5*1440)=0))*$B$5","v":0},{"$f":"QUOTIENT(A7-$C$5,$A$5)*$B$5-(MOD(MINUTE(A7),15)=0)*$B$5","v":0},{"$f":"INT((A7-$C$5)/\"00:15\")*$B$5-(MOD(MINUTE(A7),15)=0)*$B$5","v":0},{"$f":"(INT((A7-$C$5)/\"00:15\")-(MOD(MINUTE(A7),15)=0))*$B$5","v":0}]
[{"$f":"IF(MOD(MINUTE(A8),15),QUOTIENT(A8-$C$5,$A$5)*$B$5,QUOTIENT(A8-$C$5,$A$5)*$B$5-$B$5)","v":0},{"$f":"(QUOTIENT(A8-$C$5,$A$5)-(MOD(MINUTE(A8),$A$5*1440)=0))*$B$5","v":0},{"$f":"QUOTIENT(A8-$C$5,$A$5)*$B$5-(MOD(MINUTE(A8),$A$5*1440)=0)*$B$5","v":0},{"$f":"INT((A8-$C$5)/\"00:15\")*$B$5-(MOD(MINUTE(A8),15)=0)*$B$5","v":0},0]
[2,{"$f":"(QUOTIENT(A9-$C$5,$A$5)-(MOD(MINUTE(A9),$A$5*1440)=0))*$B$5","v":2},{"$f":"QUOTIENT(A9-$C$5,$A$5)*$B$5-(MOD(MINUTE(A9),$A$5*1440)=0)*$B$5","v":2},2,2]
[2,{"$f":"(QUOTIENT(A10-$C$5,$A$5)-(MOD(MINUTE(A10),$A$5*1440)=0))*$B$5","v":2},{"$f":"QUOTIENT(A10-$C$5,$A$5)*$B$5-(MOD(MINUTE(A10),$A$5*1440)=0)*$B$5","v":2},2,2]
[4,{"$f":"(QUOTIENT(A11-$C$5,$A$5)-(MOD(MINUTE(A11),$A$5*1440)=0))*$B$5","v":4},{"$f":"QUOTIENT(A11-$C$5,$A$5)*$B$5-(MOD(MINUTE(A11),$A$5*1440)=0)*$B$5","v":4},4,4]
[4,{"$f":"(QUOTIENT(A12-$C$5,$A$5)-(MOD(MINUTE(A12),$A$5*1440)=0))*$B$5","v":4},{"$f":"QUOTIENT(A12-$C$5,$A$5)*$B$5-(MOD(MINUTE(A12),$A$5*1440)=0)*$B$5","v":4},4,4]
[6,{"$f":"(QUOTIENT(A13-$C$5,$A$5)-(MOD(MINUTE(A13),$A$5*1440)=0))*$B$5","v":6},{"$f":"QUOTIENT(A13-$C$5,$A$5)*$B$5-(MOD(MINUTE(A13),$A$5*1440)=0)*$B$5","v":6},6,6]
[14,{"$f":"(QUOTIENT(A14-$C$5,$A$5)-(MOD(MINUTE(A14),$A$5*1440)=0))*$B$5","v":14},{"$f":"QUOTIENT(A14-$C$5,$A$5)*$B$5-(MOD(MINUTE(A14),$A$5*1440)=0)*$B$5","v":14},14,14]
[24,{"$f":"(QUOTIENT(A15-$C$5,$A$5)-(MOD(MINUTE(A15),$A$5*1440)=0))*$B$5","v":24},{"$f":"QUOTIENT(A15-$C$5,$A$5)*$B$5-(MOD(MINUTE(A15),$A$5*1440)=0)*$B$5","v":24},24,24]