한빛출판네트워크

출처: 회사에서 바로 통하는 엑셀 2003

인사부에서 근무하는 김대리는 직원의 출/퇴근 시간을 기록하는 데이터를 바탕으로 시간외 근무수당을 계산하려고 합니다. 단 출근시간을 올림하고 퇴근시간은 내림하여 계산하려고 하는데 어떻게 해야 할까요? 예를 들면, 8시 52분 출근시간은 9시 정각으로 오후 8시 25분은 오후 8시 20분으로 각각 내림하려고 합니다.

시간을 올림, 내림하는 방법은 각각 ROUNDUP, ROUNDDOWN 함수를 사용하시면 됩니다. 단, 시간이기 때문에 다음과 같은 사전 지식이 필요합니다.

엑셀에서 날짜/시간 값은 숫자로 인식하며, 하루를 1로 인식합니다. 그러므로 시간을 제대로 올림하거나 내림하기 위해서는 별도로 숫자 계산을 해야 합니다. 즉, 분을 계산하려면 1440을 곱해서 계산해야 합니다.



다음은 직원들의 출/퇴근 시간을 기억해뒀다가 시간외 근무수당을 계산하는 방식에 대한 예제입니다. 근무시간은 퇴근시간에서 출근시간을 뺀 근무시간이 적혀 있습니다.



올림과 내림을 적용하여 시간을 계산하고, 1시간당 시간외 근무수당을 지급하니 다시 계산된 시간을 시간별로 계산하는 작업을 진행해보겠습니다. 다음은 계산이 모두 종료된 결과입니다.



E열의 내림, 올림적용 계산의 경우 다음과 같은 수식이 사용되었습니다.

=(ROUNDOWN(C2*1440, -1)/1440) – (ROUNDUP(B2*1440, -1)/1440)

퇴근시간은 내림을 적용하기 위해 분을 일 단위로 변환하려고 숫자 1440(60(분)*24(시))를 곱한 다음 일(분) 단위를 내리고 다시 시간으로 표시하기 위해 숫자 1440로 나눕니다. 출근시간은 방법은 같고 ROUNDUP 함수를 사용해 분 단위로 값을 올림니다.

이렇게 계산된 결과값은 E열에서 확인해볼 수 있으며, 다시 이 수를 시간외 근무에 해당하는지 여부를 판단하기 위해 1시간, 2시간 단위로 변환합니다.

사용된 수식은 =IF(INT((E2 – TIME(9,0,0))*24), INT((E2 – TIME(9,0,0))*24)/24, “해당없음”) 입니다. 하나씩 풀어서 설명하면 다음과 같습니다.

(E2(근무시간) – 9시간(정상근무시간))*24(시간을 계산하기 위해 24를 곱합니다)

이렇게 계산된 결과값이 1(시간)이 넘어야(계산 결과값을 INT 함수로 되돌리는 것은 1을 넘지 못하는 수는 0을 반환하여 주기 때문에 IF 함수에서 논리적으로 판단할 때 FALSE로 인식할 수 있기 때문입니다) 초과 수당을 받을 수 있기 때문입니다.

반환된 결과값 중 ##########은 날짜/시간값이 음수 결과값을 나타내고 있다는 의미입니다. 날짜, 시간에는 음수 개념이 없기 때문에 표현하지 못한다는 걸 말하는 기호입니다. 즉, 결과값이 음수를 반환한다는 것은 근무자가 정상적인 9시간 근무를 채우지 못했다는 의미로 받아들이면 됩니다.