■ 목차
1. 엑셀 팁/질문 : 총 근무시간 구하기
2. 엑셀은 날짜, 시간을 어떻게 관리하는가?
3. 총 근무시간 구하기
4. 근무시간에 대한 지급 금액 구하기
5. 00시 넘어가는 경우의 근무시간 구하기
** 핵심 요약
1. 엑셀 팁/질문 : 총 근무시간을 구하고 일별 근무시간의 합계를 구하고 싶은데 결과 값이 이상하게 나옵니다.
- 일별 근로시간을 모두 합계를 하게 되면 215시간처럼 총합계시간으로 구하고 싶은데 잘 안 됩니다.
- 그리고 시간당 임금으로 지급금액을 구하고 싶은데 자꾸 다른 값이 나옵니다. 어떻게 해야 하나요?
- 출근, 퇴근시간을 관리하고 "퇴근시간 - 출근시간"으로 근무시간을 구하고 싶은데 00시을 넘어서 근무를 하는 경우 (21:00 ~ 06:00)에 어떻게 근무시간을 구해 주나요?
2. 엑셀은 날짜, 시간을 어떻게 관리하는가?
- 엑셀의 날짜, 시간관련한 모든 이슈는 엑셀이 날짜, 시간을 어떻게 관리하는지를 이해하는 것이 중요합니다.
- 간혹 이해되지 않는날짜, 시간의 오류와 결괏값은 우리가 사용하고 있는 엑셀 프로그램과 눈높이가 다르기 때문에 에 생기는 오해입니다. ^^
- 엑셀은 날짜, 시간을 모두 숫자로 관리를 합니다.
- 날짜 시간 데이터를 관리하는데 있어서 중요한 점은 1) 날짜, 시간은 정해진 입력형식을 준수해야 합니다.
- 2) 그러면 엑셀은 날짜, 시간을 내부적으로는 숫자로 데이터 값을 관리하고 3) 사용자에게는 익숙한 날짜, 시간 서식으로 자동으로 변화해서 보여 줍니다.
1) 날짜의 입력 형식
- 슬래쉬 (" / ") 혹은 하이픈 (" - ")로 구분하여 입력합니다. "2023/01/01" 혹은 "2023-01-01"과 같이 입력을 합니다.
- 보통 우리가 사용하고 있는 "2023년 1월 1일" 정도까지는 엑셀이 자동으로 인식을 해줍니다.
- 그 외의 다른 형식은 잘못된 데이터 값을 가지게 되거나 "문자"로 취급이 될 수 있으니 가능한 사용 하지 말아야 할 것입니다.
2) 날짜의 실제 값 ( = 엑셀이 관리하는 숫자)
- 이렇게 우리에게 익숙하고(?) 엑셀과 약속된 형식으로 입력을 하면 엑셀은 "날짜 입력"을 인지하고 입력된 날짜에 해당하는 진짜 값을 내부적으로 가지게 됩니다.
- 이 날짜의 진짜 값은 "1900/01/01"을 숫자 1을 기준으로 하루에 + 1씩 더해서 "2023/01/01"에 해당하는 숫자 값 = 44927이 날짜 데이터의 진짜 값이 됩니다.
- 날짜의 더하기, 빼기 모두 진짜 값을 기준으로 하고 "하루 = 숫자 1"을 기준으로 하기 때문에 "날짜2 - 날짜 1 = 경과 일수"로 계산이 가능하게 됩니다.
- 날짜의 진짜 값 "44927"에는 년, 월, 일 관련한 값이 보이지 않습니다. 엑셀이 계산을 해서 보여 주기 때문입니다.
3) 우리가 입력 후 보는 값은 날짜 형식의 셀 서식이 적용된 결과입니다.
- 날짜의 진짜 값은 "44972"와 같이 정수이지만 엑셀은 사용자의 편의를 위해서 "2023-01-01"과 같이 날짜 형식의 셀 서식을 적용하여 보여줍니다.
- 이렇게 날짜는 1) 입력하는 형식 2) 정수로된 진짜 값 3) 보이는 형식 (날짜 서식)으로 구성이 되어 있으며
- 모든 날짜 계산은 "정수의 진짜 값"을 기준으로 이루어 집니다.
- 1) 입력방식은 엑셀에게 정확하게 날짜 데이터 입력임을 지정해주는 행위가 되고
- 3) 보여지는 형식(날짜 서식)은 엑셀의 사용자를 위한 서비스입니다.
- 2) 실제 계산은 "진짜 날짜 값인 정수"를 기준으로 이루어 지게 됩니다.
4) 시간의 입력, 실제 데이터 값, 보여지는 형식
- 이렇게 시간도 동일한 걔념으로 1) 시간의 입력 형식이 존재하고 2) 시간의 실제 값 (소수) 3) 시간의 보이는 형식 (시간 서식)이 존재합니다.
- 시간은 "숫자 1 = 하루"라는 개념에서 "1시간 = 1/24"라고 정의 되고 분(60분), 초(60초)도 동일한 방식으로 정의가 됩니다.
- 그리고 입력은 "콜론 ( " : " )"으로 구분하여 "시간:분:초(hh:mm:ss)" 혹은 "시간:분(hh:mm)"으로 입력해야 합니다.
- "1시"의 진짜 값이 "1/24"이기 때문에 1시 * 2 = 2 가되는 것이 아니라 1/24*2 = 1/12라는 값이 계산이 됩니다.
- 비록 셀 서식으로는 "2:00"라고 보이기는 하지만 이는 숫자 2가 아니고 2시 이기 때문에 시급(15,000 원/시간)을 곱하게 되면 잘 못된 값을 가지게 됩니다.
5) 날짜, 시간의 셀 서식
- 그리고 마지막으로 숫자로 관리된는 날짜, 시간을 우리가 원하는 형태로 보기 위해서 셀 서식을 지정해주게 되는데. 아래와 같습니다.
- 날짜의 기본적인 서식은 "yyyy", "mm", "dd" 입니다. 요일은 한글표시는 는 "aaa", "aaaa"이고 영문 표시는 "ddd", "dddd"입니다.
- 시간의 기본 서식은 "hh", "mm", "ss"입니다.
- 그리고 시간의 경우 경과 시간 계산에서 예를 들어 시간이 60분 이상이 되면 분 → 시간을 더해주는 방식으로 계산이 되는데
- 이를 60분 이상으로 총계를 구할 수 있게 해주는 서식이 대괄호 ( " [ ] ")입니다. - 분을 시간으로 안 넘기고 모아( [ ] ) 주는 서식으로 보시면 됩니다.
- [hh], [mm]과 같이 서식을 적용해 주시면 됩니다.
- 지금까지 설명된 부분만 명확하게 이해를 하시면 더 이상 날짜, 시간 데이터를 만들고 계산하는데 전혀 어려움이 없을 듯합니다.
- 그런데 내용이 조금 어려운 부분이 있네요. ㅠㅠ 찬찬히 읽어 보시고 실제로 사용을 해보시기 바랍니다.
- 서론이 너무 길었습니다. 날짜와 시간에 대해서 그 어떤 설명을 해도 상기 부분의 이해가 없으면 혼돈만 가중되고 다시 보게되면 잊어버리게 됩니다.
- 그래서 다른 과정에서 조금씩 설명을 드렸지만 다시금 전체 개념에 해당하는 부분을 설명드리다 보니 얘기가 길어 졌습니다.
3. 총 근무시간 구하기
- 총 근무시간을 구하는 방식은 2가지입니다.
- 첫 번째는 "셀 서식"을 적용하여 보이는 시간을 24시간이 넘어가도 정확하게 보이게 하는 방법이 있고
- 두 번째는 1시간 = 1/24의 값을 가지지만 우리가 일반적으로 알고 있는 숫자 1과 같은 값으로 변화하는 방식입니다.
- 두 번째 방식은 구해진 진짜 시간 값(1/24)에 곱하기 24 ( * 24)을 해주면 됩니다. 근무시간에 시간급여 단가를 곱해주는 경우에는 이렇게 사용해 줘야 합니다.
- 첫 번째는 셀 서식 사용자 지정에서 "[h]:mm"이라고 시간의 총계를 볼 수 있도록 설정하시면 됩니다.
- 메뉴 패스 : 셀 서식(Ctrl + 1) > 표시 형식 > 사용자 지정 - [h]:mm
- 이때 주의 하실 점은 "첫 번째" 방식의 셀 서식은 "시간 형식"이고 "두 번째" 방식의 셀 서식은 "숫자 형식"입니다.
- 셀 서식을 정확하게 설정을 해주셔야 정확한 값을 볼 수 있습니다.
4. 근무시간에 대한 지급 금액 구하기
- 11시간이라는 시간 데이터의 실제 값은 11/24입니다.
- 그래선 "15,000원/시간"의 형식의 시간당 임근을 곱하여 지급금액을 구하기 위해서는
- 11/24가 아닌 11이라는 숫자값이 필요하고 우리는 * 24을 해줌으로 해서 구할 수 있습니다.
- 보이는 11:00과 시간 데이터의 실제 값(11/24)과의 차이에서 오는 계산 오류라고 할 수 있습니다.
5. 00시 넘어가는 경우의 근무시간 구하기
- 이 경우 엄밀히 말하자면 "2023-10-01 21:00" ~ "2023-10-02 06:00"의 경과 시간이라서
- 그냥 ("퇴근일자 시간" - "출근일자 시간")을 계산해주면 되지만 그러려면 날짜+시간을 함께 데이터로 관리를 해줘야 하는 불편함이 있어서
- 그래서 간단하게 If 함수로 (퇴근시간 < 출근시간) 경우 즉 하루가 넘어가는 경우 = (퇴근시간 - 출근시간 + 1)
- (퇴근시간 > 출근시간)인 경우 즉 하루가 안 넘어가는 경우는 = (퇴근시간 - 출근시간)으로 근무시간을 계산할 수 있습니다.
- 아니면 Mod 함수라고 나머지를 구해주는 함수를 이용해서 "= Mod (퇴근시간 - 출근시간, 1)"라고 사용하는 방식이 있습니다.
- Mod 함수는 나머지를 구해주는 함수이지만 나머지를 구할 숫자가 음수인 경우도 우리가 원하는 경과 시간을 구할 수 있게 됩니다.
- 이게 상세한 내용은 음수의 나머지값은 어떻게 구하나부터 시작해서 조금 복잡해서 생략을 하도록 하겠습니다.
- 궁금하신 분은 Mod함수의 음수의 나머지로 구글링을 해보시기 바랍니다.
- 두 경우 모두 셀 서식은 "시간 형식"을 사용해 줍니다.
- 날짜, 시간에 대해서는 엑셀의 규칙을 이해하지 못하면 어렵고 오류가 많이 발생되는 작업이 됩니다.
- 하지만 데이터에서 특히 날짜 같은 경우는 거의 빠지지 않고 사용이 되는 형태이기 때문에 개념만 잘 이해를 하시면 많은 도움이 될 듯합니다.
** 핵심 요약 : 총 근무시간 구하기 ( 24시간이 초과되는 총 합계 시간을 구해보자)
1. 엑셀 팁/질문 : 퇴근시간 - 출근시간으로 총 근무시간을 구하고 일별 근무시간의 합계를 구하고 싶은데 결과 값이 이상하게 나옵니다.
- 총 근무시간 구하기, 근무시간에 대한 지급 금액 구하기, 00시 넘어가는 경우의 근무시간 구하기
2. 엑셀은 날짜, 시간을 어떻게 관리하는가?
- 날짜, 시간 모두 숫자로 관리를 한다.
- 1) (입력되는 형식) 날짜, 시간의 정해진 입력 형식을 준수해야 한다.
- 2) (실제 값 = 계산에 사용) 날짜, 시간을 숫자로 데이터 값을 내부적으로 관리한다.
- 3) (보이는 형식, 날짜 서식) 사용자에게는 익숙한 날짜, 시간 서식으로 자동으로 변화해서 보여준다.
3. 총 근무시간 구하기
- 셀 서식으로 보고자 한다면 셀 서식 > 표시 형식 > 사용자 지정 - "[h]:mm"으로 지정해 준다.
- 1시간, 2시간의 1, 2 숫자로 구하고 싶다면 시간 데이터 값에 * 24을 해준다 (하루 = 숫자 1 = 1/24 시간)
- 시간 형식인지 숫자 형식인지 구분하여 셀 서식에 주의하여 값을 표시한다.
4. 근무시간에 대한 지급 금액 구하기
- 시간 데이터 11:00의 실제 값은 11/24이다. 지급 단가를 곱하여 금액을 구하기 위해서는 시간 데이터 * 24를 해줘야 한다.
- 보이는 시간(11 : 00)의 실제 값은 (11/24)로 차이가 있으며 계산의 오류를 만든다.
5. 00시 넘어가는 경우의 근무시간 구하기
- If 함수로 퇴근시간 < 출근시간으로 하루가 넘어가는 경우 = (퇴근시간 - 출근시간 +1)를 해주고
- 퇴근시간 > 출근시간으로 하루가 안 넘어가는 경우 = (퇴근시간 - 출근시간)으로 구해 준다.
- Mod 함수는 나머지를 구해주는 함수로 "=Mod(퇴근시간-출근시간, 1)"로 원하는 근무시간을 구할 수 있다.
[세상의 모든 엑셀 (엑셀팁, 질문)] - 999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
[세상의 모든 엑셀 (엑셀팁, 질문)] - 001. (엑셀 팁, 질문) 누적 합계 구하기 - 절대참조/상대참조, 빠른 분석 도구 [세상의 모든 엑셀 (엑셀팁, 질문)] - 002. (엑셀 팁, 질문) 날짜 칸 나누어 입력하기
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글