[엑셀] 급여관리 - 지문인식 출퇴근기록기를 이용한 출/퇴근시간에 의한 근로시간(근무시간) 계산 수식 작성 방법.

안녕하세요.

행아아빠 입니다.

 

이번 #엑셀 내용은

지문인식 출, 퇴근 기록기로 찍힌 출, 퇴근 시간을 엑셀로 변환해서

근무시간 계산 수식을 작성하는 내용입니다.

​

근무시간을 계산하는 수식은

이미 제 블로그에 근무시간을 계산하는 함수와 수식 작성 방법에 대해서는 한번 설명드린 적이 있으니,

아래 링크를 참조해 주시면 좋을 것 같습니다.

 

[엑셀] 급여관리 - 근로시간 자동계산 수식 작성 방법.

https://hang-a-fafa.tistory.com/10

▲ 위 내용에서는 출, 퇴근 시간만큼은 직접 입력을 해 주어야만 근무시간이 계산되는 수식이었으나,

    지문인식 출, 퇴근 기록기를 이용하여 내역을 엑셀로 다운로드할 수 있다면

    출, 퇴근 시간도 직접 입력해 줄 필요 없이 엑셀로 다운로드한 내역을 해당 근무 카드 양식에 출, 퇴근 시간만 결과값으로 입력될 수 있는

    수식만 작성해 주시면 급여 담당자는 직접 계산하고 입력하는 시간이 급격하게 줄어 들을 수 있겠습니다.

---

▼ 제게 질문 주신 내용은 아래와 같습니다.

지문인식 출퇴근 기록기를 쓰고 있는데

저희 현장이 근무 유형이 여러 가지가 있어 프로그램에서도 제대로 계산이 되지 않아 기록만 뽑아 엑셀로 계산해서 사용해야 하는데 많은 어려움이 있습니다.

​

근무유형에 상관없이 총 근무시간이 나오는 엑셀 수식 좀 알려주세요..ㅠㅠ

​

근무유형

1. 24시간 2교대(07:00~다음날 07:00)

2. 3교대 : 오전(06:30~18:30) 오후(18:30~다음날 06:30)

3. 16시간 2교대 : 오전(06:00~14:00), 오후(14:00~22:00)

4. 정상 : 08:00~17:00

​

시간에 상관없이 근무시간 구할 수 있는 수식이 필요합니다.

---

▲ 출, 퇴근 기록기를 엑셀 파일로 다운로드한 예시 파일인 것 같습니다.

    질문자분께서 개인 정보 유출 우려를 염려하셔서

    ①직원명을 전부 "홍길동" 으로 작성해 주셨으나,

    ②근태기 사용자ID or D 열의 사번으로 구분을 할 수 있을 것 같습니다.

    그리고 ③H, I, J 열의 출입구분의 출, 퇴근 구분과 출입일의 날짜, 출입시간의 출, 퇴근 시간까지

    ① ~ ③까지만 알면 기본 양식이 작성된 출, 퇴근 시간에 출근시간과 퇴근시간을 각각 결과값으로 불러오고,

    추가적인 조건으로 직원명과 근태기사용자ID가 일치하는 값을 최종 결과값으로 가져올 수 있으면 되겠지요!!

▲ 다시 한번 본문에서 수식을 작성해 보겠습니다.

    수식을 작성하기 위해 약식으로 양식을 만들어서 수식을 적용시켜 보았습니다.

    해당 양식에서 참조할 것은

    노란색 음영 처리되어 있는 셀의 이름과, 근태기ID, 날짜의 첫 시작일 이렇게 3가지는 입력을 해 주셔야 3가지 조건에 충족하는 값을

    결과값으로 가져올 수 있습니다.

​

    P, Q, R 열에 이미 수식을 입력해 놓았기 때문에 결과값이 이미 작성되어 있는 것을 확인하실 수 있는데요.

    그 수식은 ↓ 아래와 같이 작성 할 수 있겠습니다.

    출근시간

    =IFERROR(LOOKUP(1,1/(($E$2:$E$1992=$N$1)*($H$2:$H$1992=$P$1)*($I$2:$I$1992=$N2)

      *($G$2:$G$1992=$O$1)),$J$2:$J$1992),"")

​

    퇴근시간

    =IFERROR(LOOKUP(1,1/(($E$2:$E$1992=$N$1)*($H$2:$H$1992=$Q$1)*($I$2:$I$1992=$N2)

      *($G$2:$G$1992=$O$1)),$J$2:$J$1992),"")

​

    총 근무시간

    =IFERROR(IF(AND(P2="",Q2=""),"",IF(AND((P2*24)>=1,Q2=""),"확인필요",

    IF(AND(P2="",(Q2*24)>=1),"확인필요",

    ROUND(IF(P2<Q2,(Q2-P2)*1440,IF(OR(P2="",Q2=""),"",IF(P2>Q2,(1+Q2-P2)*1440)))/60,1)))),"")

​

    위와 같은 수식을 입력하게 되면,

    근태기 기록기에서 다운로드한 파일의 범위에서 출근시간과 퇴근시간을 결과값으로 자동으로 입력하게 되고,

    출, 퇴근시간의 총 근무시간을 계산할 수 있습니다.

    다만, 근태기 기록기를 사용한다고 해서 오류가 없는 것은 아닐수 없을 거예요!!

    분명 출근이나 퇴근 중 무언가를 기록하지 않은 근무자가 있을 수 있고, 혹은 근무를 했음에도 불구하고

    출, 퇴근 기록을 하지 않았을 수 있습니다.

​

    근무를 했음에도 출, 퇴근 중 아무것도 기록하지 않은 근무자는 직접 확인하는 방법 이외에는 방법이 없을 것 같고,

    출, 퇴근시간 중 어느 하나만이라도 기록을 한 근무자라면,

    출근시간에만 결과값이 입력되어 있거나, 퇴근시간에만 결과값이 입력되어 있을 것이기 때문에 총 근무시간이 오류가 날것이기 때문에,

    출근시간과 퇴근시간 둘 중 한곳에만 기록이 되어 있다면 총 근무시간에는 "확인 필요"라는 결과값이 나오도록 하여,

    추후 직접 확인할 수 있도록 하면 될 것 같습니다.

​

    이미 한번 블로그에 작성되어 있는 내용이기는 하지만,

    질문에 따라 또는 상황에 따라 적용이 안 되나 싶기도 하지만,

    결국 작성된 수식 중에서 셀의 위치만 변경해 주면 적용되는 수식들입니다.

---

 

 

    그럼 오늘 내용도 많은 분들께 도움이 되셨으면 좋겠습니다.

    좋은 하루 보내세요~^^!