1주의 6,7일째 연장근로 시작 및 종료 시각을 표시하는 수식

[정보원]

2021년 5월 11일 

시간외근무명령서들과 월별 연장근로대장에서, 1주의 6,7일째의 연장근로의 시작과 종료 시각을 제대로 표시하게 했습니다.

1주의 6,7일째란

1주의 기산점이 일요일이면 매주 금요일과 토요일

1주의 기산점이 월요일이면 매주 토요일과 일요일

1주의 기산점이 1일이면 매월 6일과 7일, 13일과 14일, 20일과 21일, 27일과 28일입니다. 

시간외근무명령서와 연장근로대장에서 연장근로의 수식 

연장근로시간 :

=IFERROR(VLOOKUP($N308,INDIRECT($O$298&"!$N$283"):INDIRECT($O$298&"!$AS$297"),DAY($P$300)+1,FALSE),"")

연장근로의 종료시각 

=IFERROR((IF(Q308=0,"",VLOOKUP(VLOOKUP($N308,INDIRECT($O$298&"!$N$106"):INDIRECT($O$298&"!$AS$120"),DAY($P$300)+1,FALSE),INDIRECT($O$298&"!$D$8"):INDIRECT($O$298&"!$F$17"),3,FALSE))*24+(Q308-(VLOOKUP(VLOOKUP($N308,INDIRECT($O$298&"!$N$106"):INDIRECT($O$298&"!$AS$120"),DAY($P$300)+1,FALSE),INDIRECT($O$298&"!$D$8"):INDIRECT($O$298&"!$h$17"),5,FALSE)-IF(VLOOKUP($N308,INDIRECT($O$298&"!$N$255"):INDIRECT($O$298&"!$AS$269"),DAY($P$300)+1,FALSE)=5,0,8))))/24,"")

연장근로의 시작시각

=IF(VLOOKUP($N308,INDIRECT($O$298&"!$N$255"):INDIRECT($O$298&"!$AS$269"),DAY($P$300)+1,FALSE)=5,VLOOKUP(VLOOKUP($N308,INDIRECT($O$298&"!$N$106"):INDIRECT($O$298&"!$AS$120"),DAY($P$300)+1,FALSE),INDIRECT($O$298&"!$D$8"):INDIRECT($O$298&"!$h$17"),2,FALSE),IF(P308*24>Q308,(P308*24-Q308)/24,(24-(Q308-P308*24))/24))

일일연장근로시간표에서 매주 6,7일째의 수식 

=IF(OR(T148=0,T191>0),0,IF(T$298=6,IF(COUNTIFS(O148:S148,">0",O191:S191,0)<5,T148-8,IF(COUNTIFS(O148:S148,">=8",O191:S191,0)>=5,T148,T148+SUMIF(O283:S283,"<0"))),IF(T$298=7,IF(COUNTIFS(N148:S148,">0",N191:S191,0)<5,T148-8,IF(COUNTIFS(N148:S148,">0",N191:S191,0)=5,IF(COUNTIFS(N148:S148,">=8",N191:S191,0)=5,T148,T148+SUMIF(N283:S283,"<0")),T148+IF(S283<0,S283,0))),T148-8)))

  
근무가 없거나 휴일이면 0 
IF(OR(U148=0,U191>0),0, 

6일차이면 
IF(U$298=6, 

직전 5일간 근무가 있고 휴일 아닌 날이 5일 미만이면, 당일근로시간-8,  
IF(COUNTIFS(P148:T148,">0",P191:T191,0)<5,U148-8, 

아니면(=5일 이상이면) 

직전 5일간 근로시간이 8시간이상이면서 휴일 아닌 날이 5일이상이면 당일근로시간 전체, 아니면 당일근로시간-(직전 5일간 8시간에서 모자란 시간의 합계)  
IF(COUNTIFS(P148:T148,">=8",P191:T191,0)>=5,U148,U148+SUMIF(P283:T283,"<0"))), 

아니면(=6일차가 아니면) 

7일차이면 
IF(U$298=7, 

직전 6일간 근무가 있고 휴일 아닌 날이 5일 미만이면, 당일근로시간-8,  
IF(COUNTIFS(O148:T148,">0",O191:T191,0)<5,U148-8, 

아니면(=5일 이상이면) 

직전 6일간 근무가 있고 휴일 아닌 날이 5일이면 
IF(COUNTIFS(O148:T148,">0",O191:T191,0)=5, 

직전 6일간 근로시간이 8시간이상이면서 휴일 아닌 날이 5일이면 당일근로시간 전체, 아니면 당일근로시간-(직전 6일간 8시간에서 모자란 시간의 합계)  
IF(COUNTIFS(O148:T148,">=8",O191:T191,0)=5,U148,U148+SUMIF(O283:T283,"<0")), 



아니면(=6일이면)   

6일차 연장근로시간이 마이너스이면 당일근로시간에 6일차 마이너스 연장근로시간을 더하고, 아니면 0시간을 더합니다. 
U148+IF(T283<0,T283,0) 
) 
), 

아니면 (6일차도 7일차도 아니면) 당일근로시간-8 
U148-8 
) 
) 
) 

[정보원]

일일연장근로시간표에서 매주 6,7일째의 수식을 찬찬히 읽어 보시면
프로그래밍의 재미를 맛보실 수 있을 겁니다.

[김동찬]

고맙습니다. 선생님