이전 포스팅에서 이어집니다.
[구글 스프레드 시트 활용] 출퇴근 시간 기록 문서 만들기-2(AVERAGE, COUNTA, 조건부 서식 활용하기)
[구글 스프레드 시트 활용] 출퇴근 시간 기록 문서 만들기-2(AVERAGE, COUNTA, 조건부 서식 활용하기)
이전 포스팅에서는 날짜 채우기까지 해 보았습니다. [구글 스프레드 시트 활용] 출퇴근 시간 기록 문서 만들기-1(ARRAYFORMULA, INDIRECT, DATEVALUE, EOMONTH 함수 활용하여 날짜 채우기) [구글 스프레드 시��
moond.tistory.com
지난 포스팅에서는 월간 시트에 날짜 입력하기, 조건부 서식 입력하기, 출근일과 평균 출퇴근시간 계산에 대하여 다루어보았습니다.
이번 포스팅에서는 초과 근무시간을 계산해보도록 하겠습니다.
우선 하루 총 근무시간부터 산출해봅시다.
하루의 정규 근무시간이 8시간이라고 가정합니다.
중간에 1시간 점심시간이 있습니다.
퇴근시간-출근시간-점심시간=하루 총 근무시간
이렇게 계산할 수 있습니다.
그럼 일단 점심시간을 시간 형식으로 적어줍니다.
그리고 셀 추가로 하루 총 근무시간 셀을 만들어줍니다.
왼쪽에 추가할지 오른쪽에 추가할지 선택할 수 있습니다.
저는 퇴근시간 셀 오른쪽에 만들어주도록 하겠습니다.
그런데 점심시간 항목이 고정된 빼기 값이므로
퇴근시간-출근시간-점심시간 식을 적용했을 때,
빈칸이더라도 총 근무시간 셀에 23:00라는 값이 표출됩니다.
이를 방지하기 위해 IF 함수를 사용해줍니다.
=IF(COUNTA(D5:E5)=2,E5-D5-J$1,)
IF 함수에 대해서는 이전 포스팅에서 설명했습니다.
출퇴근 시간 셀 두개의 칸이 모두 채워져있는 경우에만 퇴근시간-출근시간-점심시간 식의 값을 반환하고,
빈칸일 경우 그대로 빈칸을 반환하는 식입니다.
TRUE 또는 FALSE 시 빈칸으로 두고싶을 경우에는 값을 입력하지 않고 콤마만 입력해주면 됩니다.
이 식을 긁어내려 전체 셀에 적용해줍니다.
그럴경우 한칸만 적혀있으면 빈칸으로 반환됩니다.
비슷한 방식으로 초과 근무시간 셀도 만들어줍니다.
초과 근무시간=근무시간-표준 근무시간
으로 구할 수 있습니다.
비교를 위한 표준 근무 시간 셀을 먼저 만들어 준 뒤 수식을 적용하도록 합니다.
=IF(F5>K$2,F5-K$2,)
총 근무시간이 표준 근무시간을 초과했을 경우, 총 근무시간에서 표준 근무시간을 뺀 값을 구하는 수식입니다.
여기서 K2 셀이 표준 근무 시간입니다.
해당 값을 바탕으로 평균 근무시간과 총 추가 근무시간 값까지 구하면 완료입니다.
평균 근무시간은 AVERAGE 함수를, 총 초과 근무시간은 SUM 함수를 이용하면 됩니다.
자, 이제 월간 시트가 완성되었습니다.
월간 시트 작성 마무리에 앞서, 출퇴근시간 기록하는 팁을 알려드리겠습니다.
NOW 함수
=NOW()
괄호 안에 인수가 필요없는 함수로,
입력 시 현재 시간을 표출합니다.
GMT 기준이 아니고, 이용중인 기기의 시스템 기준의 시간을 반환하는 식입니다.
해당 식을 이용하면 손쉽게 현재 시간을 입력할 수 있습니다.
하지만 이 식의 단점은,
시간의 흐름에 따라 NOW 함수를 이용하여 반환한 시간도
함께 변경된다는 사실입니다.
때문에 결과값을 복사한 후
값만 붙여넣기를 통하여 숫자값으로 만들어줘야 합니다.
단축키로는 Ctrl+Shift+V 입니다.
이제 월간 시트 만들기는 어느정도 마무리 된 것 같습니다.
다음 포스팅에는 12월까지 시트를 복사하는 방법과
전체통계 시트 만드는 내용을 다뤄보도록 하겠습니다.
댓글