구글 스프레드시트, 엑셀 시간(Time) 계산 - 근무시간 계산, 출퇴근시간계산/합계, 근퇴관리,시간반올림,반올림함수 ROUND와 MROUND,나누기함수 QUOTIENT와 MOD
미드옵 ・ 2020. 12. 18. 22:49
이전 포스트에서 기본적으로 날짜(Date)나 시간(Time) 역시 숫자(Number)라고 언급한 적이 있다.
따라서, 구글 스프레드시트, 엑셀뿐 아니라 모든 데이터 분석이나 리포트작업시 검색조건의 기준이 된는건 두가지 밖에 없게 된다.
'숫자(Number)' 또는 '문자(Text)'
이 두가지만 컨트롤 할 수 있으면 대부분의 문제를 해결할 수 있다는 거다.
👉 해당내용 관련 네이버TV : https://tv.naver.com/prism
업무상 엑셀이나 구글 스프레드시트에서 시간을 기준으로 작업해야하는게 몇가지 있을텐데... 그 중 가장 대표적인게 근태관리(출퇴근관리, 근무시간관리)의 성격의 시간계산일 것이다.
간단하게 상황을 하나 살펴보자.
근무시간(경과시간)을 계산하기 위해서는 아래처럼 종료(퇴근)시간에서 시작(출근)시간의 차이로 계산하면 되는데…
날짜가 바뀌어 기록되는 시간계산이 문제가 된다.
문제는, 교대근무 같이 근무시간 날짜가 바뀌어 기록되는 경우는 정상적으로 계산되지 않는다.
추가로 오버타임(OT)나 휴일근무에 따른 부분도 생각해을 해야 하겠지만...
여기서는 날짜(Date), 요일(Day)의 연장선이므로 시간(Time)에 대한 설명으로만 제한하겠다.
앞에서 시간(Time) = 숫자(Number)라고 이야기 했다.
위의 예제처럼 날짜가 바뀜으로 발생하는 문제 역시 시간을 숫자로 바꿔계산하면 수월하다.
서식에서 숫자로 바꿔 줌
간단하게 이 글을 쓰고 있는 날짜를 숫자로 표시되도록 해보자.
2020년 12월 18일은 숫자로 44,183이다.
하루가 숫자 1 이 되는 구성이므로 하루 24시간을 숫자서식으로 바꾸면 소수점 단위로 표시된다.
24시간에 대한 비율이다.
1일 = 1 = 24시간
즉, 스프레드시트에서 시간(Time)은 숫자로 하루 24시간에 대한 비율(%)로 표시된다.
이와 같은 내용을 이해하고 다시 시간 계산을 해보자.
=(B3-A3)*24
경과시간을 반영할 열을 수식으로 지정했다면, 종료시간과 시작시간의 차이값이 소수점으로 나오므로 시간으로 표시하기위해 24를 곱해주면 된다.
그렇다면, 문제가 되는 날짜가 바뀌는 경우를 보도록 하자.
=(B4-A4+1)*24
하루 24시간이 숫자 1에대한 비율이기 때문에 차이값이 마이너스가 나올 경우엔 1만 더해주면 된다.
이제 간단히 이 두가지 경우에 대해 IF함수로 묶어 적용한다.
비교조건은 날짜가 바뀌는 경우(종료와 시작시간의 차이값이 마이너스)로 잡는다.
=IF(B3-A3<0,(B3-A3+1)*24,(B3-A3)*24)
조금만 더 생각해보면, 복잡하게 IF함수로 비교하지 않아도, 나머지를 구하는 MOD함수를 사용해 1로 나눈 나머지를 반환하면 두가지 경우를 모두 처리할 수 있다.
=MOD(B3-A3,1)*24
또한, ARRAYFORMULA함수를 같이 사용하여 이후 추가되는 시간에 대해 자동으로 계산되도록 구성하면 좋다.
여기서 계산 시간에 대해 표시하는 방법에 대해 생각해 볼게 있다.
위 그림의 경우 경과시간이 7.53으로 되어 있는데...
착각하지 말아야 할 것은 시간을 숫자로 간주하여 계산한 것이므로, 7시간53분이 경과된게 아닌 시간기준으로 7.53시간이 경과했다는 뜻이다.
만약, 경과시간을 몇시간 몇분으로 표시하고 싶다면, 분(1시간=60분)으로 환산하여 계산하면 된다.
여기에서는 복잡하게 생각안하고 나누기를 사용하여 몫은 시간, 나머지는 분으로 분리 표시하였다.
=QUOTIENT(C6,1)
앞에서 이미 하루 24시간을 계산하였으므로 소수점을 잘라내기 위해 그냥 1로 나누었다.
아니면 그냥 =ROUNDDOWN(C6) 함수로 소수점 이하를 날려도 된다.
=MOD(C6,1)*60
분표시는 한시간이 60분이므로 시간을 나눈 나머지에 60을 곱해준다.
마지막으로 한가지를 더 생각해보자.
경과시간을 분단위로 관리한다면 상관 없지만, 30분 단위나 15분단위로 시간반올림하여 적용하는게 일반적이다.
보통은 ROUND함수를 사용하여 반올림 하겠지만...
이 경우는 ROUND함수로 30분기준 반올림 값이 나올 수 없다.
반올림 ROUND, MROUND 차이
앞에서 설명한대로 시간을 숫자로 계산하였으므로 30분은 시간으로 0.5, 15분은 0.25시간이 된다. 따라서 반올림을 적용하기 위해서는 ROUND 함수가 아닌 MROUND 함수를 사용해야 한다.
MROUND ( )한 숫자를 다른 숫자의 가장 가까운 정수배로 반올림
MROUND ( )
한 숫자를 다른 숫자의 가장 가까운 정수배로 반올림
위의 그림에서 두번째가 30분기준(0.5 적용), 세번째가 15분기준(0.25 적용)한 결과이므로 ROUND함수와의 차이점을 비교해보기 바란다.
...............................................
...................
.......
요즘에는 가볍고 좋은 솔루션이나 앱이 많이 나오긴 했으나...
실무입장에선 언제나 아쉬운 2%가 존재한다.
비유를 들자면 이건 '기성복'이므로 내 몸에 100% 맞지 않는 경우인거다.
업무보고로 기본자료에 이것 저것 추가하는 것 등, 주변 환경이 나에게 딱 맞춰주는 경우는 없다. 옷이라면 간단하게 수선이라도 해서 입겠지만, 일반적인 시스템은 모든 개인에게 1:1 맞춤을 해주지 않는다.
그러다보니 여전히 시스템에서 엑셀로 다운받아 추가작업 하는 경우가 생기기 마련이다. ( 엑셀이나 구글스프레드시트가 보완재 역할을 하는거다. )
이전에 QR코드 생성을 소개한 적이 있으니…
센스 있는사람은 직원별로 QR코드부여하고 스캐너하나 구입하면, 스캔할때마다 자동으로 타임스탬프(TimeStamp)로 출퇴근시간을 기록하고 그에 따른 비용까지 완전 자동으로 구성할 수 있다.
여기에 필요한 추가적인 지표들을 더 넣어 차트나 그래프를 구성하면, 기성복과 달리 내 몸(내 업무)에 딱 맞는 옷(업무 툴)이 하나 만들어 지는거다.
( 고급기능 없이 함수만으로 가볍게 구성할 수 있다. )
'관점'과 '기준'의 차이다.
업무적으로 필요한건 '기능(Function)'이다.
이렇게 만들면 기성복처럼 예쁘거나 화려하진 않지만, 내가 필요한건 '파티복'이 아니라 '기능복'이라는 점이다.
내 업무에 뭐가 문제고 필요한지는 본인이 가장 잘 안다.
기존의 기성복(시스템)이 내 업무에 있어 부족한 부분이 있다면 직접 수선해서 사용하는 것도 방법인거다.
어설프게 보일지는 모르지만 이 처럼 내 몸에 맞는 기능복도 없다.
