엑셀의 함수 및 기능 이용하기 TIP : 시간 계산의 활용법

 

 

Microsoft Excel을 이용하여 시간을 계산하는 방법은 단순하지만 어떤 정해진 공식이 존재하므로 그 공식을 활용하여 수식을 적용하는 것이 중요하다. 단순한 수식을 적용을 하면 사용자가 원하는 결과를 얻을 수 없거나 에러 메시지를 나타내기도 한다. 시간 차나 지나간 시간 등 여러 가지 형태로 시간을 계산하는 방법에 대해 설명한다.

 

 

 

 

 

1. 지정된 시간 후의 시간 구하기

 

 

일정한 시간 전이나 후를 구하기 위해서는 플러스나 마이너스를 사용하면 되지만 시간을 계산하기 위해서는 일정한 공식이 필요하다. 하루의 시간은 24시간으로 지정되어 있으므로 수식에 24시간을 지정해 주어야 하는데, 예를 들어 '13:30' 이후 4시간을 구하려면 '=13+4/24'와 같이 시간을 24로 나누는 공식을 적용해서 사용해야 한다. 다른 방법으로는 '13:30' + '4:00'과 같이 시간 개념으로 플러스를 해주는 공식을 적용해서 사용해야 한다.

 

 

 

 

또는 '+"4:00"'의 수식을 이용하여 사용해야 한다.

 

 

 

 

2. 시간의 차이를 구하기

 

시간 끼리 플러스 또는 마이너스는 일반 숫자와 같은 수식을 사용한다. 예를 들어 출발시각이 'E5(10:10)'이고 도착시각이 'E7(15:30)'일 경우, '=E7-E5'의 수식을 적용하여 소요시간 '5:20'의 값을 얻을 수 있다.

 

 

 

 

3. 시간 함수를 이용하여 급여 계산 하기

 

근무시간에 따른 급여 계산을 구해보자. 하루 일과중 근무시작시간 09:00부터 근무종료시간 18:00, 점심시간 13:00~14:00까지 1시간을 제외하여 하루 8시간을 근무시간으로 산정하였고, 18:00시간 이후로는 연장근무로 산정하였다. 여기서 하나의 셀에 함수를 지정하면 아래 셀 부분은 함수를 복사하여 사용하여 표 전체에 적용하도록 사용하는 것이 중요하다. 토요일과 일요일, 공휴일에는 근무를 하지 않으므로 근무시간이 0:00시간이 되도록 'ISBLANK' 함수와 'IF' 함수를 조합하여 사용한다. 정상급여와 연장급여 계산시 사간별 분별의 계산방법이 다르므로 'HOUR' 함수나 'MINUTE' 함수 등의 시간함수를 사용하는 것도 중요하다.

 

 

가. 셀이 공백인 경우에는 근무시간에 0:00으로 표시하기

 

일단 근무시간은 '퇴근시간-출근시간'으로 구할 수 있는데, 알반적으로 근무시작시간은 정해진 시간이 있으므로 근무시작시시간 전의 시간은 근무시간으로 산정하지 않는 경우가 통상적이다. 여기에서는 출근을 하지 않아 출근 시간 셀이 공백인 경우 계산을 하지 않고 0:00으로 표시하는 방법에 대해 알아보자. 이때 사용하는 함수는 'ISBLANK' 함수이다. 예를 들어 출근 시간인 'F10' 셀이 공백이라면 0을 표시하고, 그렇지 않으면 퇴근시간에서 출근시간과 점심시간을 빼는 '=G10-10/24'가 되도록 지정한다.

 

 

 

 

시간을 표시하는 셀은 12:00와 같은 형식이 되도록 셀 범위를 지정한 후 마우스 오른쪽 단추를 클릭한 후 [셀 서식] - [ 표시 형식] 탭 - [분류] - [사용자 지정] - [형식] - [h:mm]을 지정한다.

 

 

=IF(ISBLANK(F10),0,(G10-10/24))

 

 

'H10' 셀을 클릭하여 채우기 핸들을 이용하여 'H39'까지 채워서 복사해 준다.

 

 

 

 

 

나. IF함수 이용하여 야간 근무 수당 계산하기

 

18:00 이후부터는 연장 근무로 급여를 계산해야 하는데 퇴근시간이 18:00 이후인 경우에는 퇴근시간에서 18:00을 빼서 연장 근무 시간을 계산한다. 그리고 연장 근무 시간이 시작되는 18:00은 다른 셀에 입력해 놓아 계산할 수 있도록 하고, 이 셀을 참조할 때는 참조값이 바뀌지 않도록 절대참조를 지정해 놓아야 한다.

 

 

=IF(G10>18/24,G10-18/24,0)

 

 

여기서 수식에 대한 설명을 부가하자면 'G10' 셀이 18:00 이상이면 'G10'셀 값에서 '18/24' 값을 빼고 그렇지 않다면 0으로 지정하라는 의미이다.

 

 

 

다. 하루의 임금 계산하기

 

이제 하루 근무했을 때 비용이 얼마인지를 계산해 보자. 우선 시간 당 금액을 별도의 셀로 지정하여 '정상급여'일 때, 'L5'을 참조하고, '연장급여'일 때 , 'L6' 셀을 참조하도록 한다. 여기서 우리가 알아야 할 것은 Microsoft Excel에서는 시간을 일련번호라고 하는 것으로 '24'라는 시간으로 수치화한 것이므로 이 점을 잊어서는 안된다는 것이다. 그리고 'HOUR'와 'MINUTE'라는 함수는 시간의 일련번호에서 시간과 분만을 산출하는 함수이다. 이 시트에서는 근무시간을 정상근무와 연장근무로 나누어 산출된 시간에 정상급여와 연장급여를 각각 곱하고 서로 더한 다음에 하루의 임금을 산출할 수 있다.

 

 

=(HOUR(I10)*$L$5)+(MINUTE(I10)*$L$5/60)+(HOUR(J10)*$L$6)+(MINUTE(J10)*$L$6/60)

 

 

- (HOUR(I10)*$L$5) : 정상 근무 시간을 정상 근무 임금과 곱한다. 이때에는 시간에 해당하는 부분만 계산을 한다.

 

- (MINUTE(I10)*$L$5/60) : 정상 근무 시간 중 '분' 단위로 발생되는 시간에 대하여 정상 근무 임금과 곱한다.

 

- (HOUR(J10)*$L$6) : 연장 근무 시간을 연장 근무 임금과 곱한다. 이때에는 시간에 해당하는 부분만 계산을 한다.

 

- (MINUTE(J10)*$L$6/60) : 연장 근무 시간 중 '분' 단위로 발생되는 시간에 대하여 연장 근무 임금과 곱한다.