합계 셀의 서식을 [h]:mm으로 바꾸면 끝납니다. 시간 부분을 대괄호로 감싸는 것 하나가 전부입니다.
SUM이 틀린 게 아니라 화면이 덜 보여 주는 상황이기 때문입니다. 엑셀은 시간을 하루를 1로 놓고 세는데, 보통의 시간 서식은 하루가 넘어간 몫을 날짜 쪽으로 넘기고 나머지만 표시합니다. 58시간 30분이 10시간 30분으로 보이는 이유가 그것입니다.
대괄호는 그 넘기기를 하지 말라는 표시입니다. 다만 합계 셀에만 씌워야 하고, 원본 시간 셀까지 같이 바꾸면 이번에는 다른 자리가 이상해집니다. 어느 셀에 무엇을 씌울지가 이 글의 나머지입니다.
목차
합계가 10시간 30분으로 나오는 화면
값은 맞고 표시만 잘려 있습니다
예제 파일에는 엿새치 근무 시간이 들어 있습니다. 하루씩 보면 9시간, 10시간 30분, 9시간 30분, 10시간, 8시간 30분, 11시간이고 더하면 58시간 30분입니다. 그런데 합계 셀에는 10:30이 떠 있습니다.
이 수식이 실제로 들고 있는 값은 2.4375입니다. 엑셀에서 하루가 1이니 2일하고 0.4375일, 즉 이틀 열 시간 삼십 분이라는 뜻입니다. 이틀은 날짜 자리로 빠지고 남은 0.4375만 시간으로 표시되면서 10:30이 됩니다.
주의 — 수식을 고치려 들면 더 헤맵니다
SUM을 SUMPRODUCT로 바꾸거나 범위를 다시 잡아도 결과는 같습니다. 계산은 처음부터 맞았고 고칠 곳은 서식뿐이라, 수식 쪽을 손대는 시간이 그대로 낭비가 됩니다.
숫자를 직접 보면 더 분명해집니다. 합계 셀의 서식을 잠시 일반으로 바꾸면 2.4375가 뜨는데, 이 값이 시간 표기로 되돌아갈 때 정수 부분인 2가 화면에서 빠지는 것입니다. 서식을 바꾸는 일은 그 2를 화면으로 다시 불러오는 작업입니다.
스물네 시간이 아니라 마흔여덟 시간을 넘기면 이틀이 빠지고, 일흔두 시간을 넘기면 사흘이 빠집니다. 주간 합계에서는 한 바퀴만 도는 경우가 많아 그럭저럭 눈치채는데, 월간 합계는 며칠씩 빠져 있어도 숫자가 그럴듯해 보입니다.
합계 셀에만 씌운다
원본까지 바꾸면 다른 자리가 이상해집니다
대괄호 서식은 누적 시간을 보여 주는 용도입니다. 출근 시각처럼 하루 안의 한 시점을 담는 셀에 씌우면 09:00이 9:00으로 바뀌는 정도에 그치지만, 날짜가 함께 든 셀이라면 그 날짜만큼의 시간이 전부 더해져 수만 시간이 찍힙니다.
표의 맨 아래 한 줄만 다르게 두는 셈이라 나중에 보면 어색해 보일 수 있는데, 담고 있는 뜻이 다르니 서식도 다른 것이 맞습니다. 위쪽은 하루 안의 길이이고 아래쪽은 그 길이를 다 더한 누적값입니다.
여러 사람이 돌려 쓰는 파일이라면 합계 줄 옆에 단위를 적어 두는 편이 안전합니다. 58:30이 58시간 30분인지 58분 30초인지는 표를 처음 보는 사람에게 자명하지 않습니다.
지금 해보세요 — 확인
출근 시각이 든 칸과 하루치 근무 시간이 든 칸은 둘 다 하루 안에서 끝나므로 보통의 시간 서식이면 충분합니다. 여러 날을 더한 합계 한 칸에서만 24시간을 넘으니, 대괄호가 필요한 자리도 거기 하나입니다.
주의 — 날짜가 함께 든 셀에는 씌우지 않습니다
날짜와 시각이 한 칸에 들어 있는 값에 대괄호를 씌우면 그 날짜만큼의 시간이 전부 더해져 수만 시간이 찍힙니다. 그런 칸은 날짜 서식과 시각 서식을 나란히 쓴 코드로 둡니다.
열 전체를 선택한 상태로 서식을 바꾸는 습관이 여기서 사고를 냅니다. 합계 한 칸만 잡고 바꾸면 되는 일인데, 열을 통째로 잡으면 위의 개별 시간까지 따라 바뀝니다.
서식 코드를 넣는 자리
목록에는 없어서 직접 쳐 넣어야 합니다
셀 서식 창의 시간 범주에도 비슷한 항목이 있지만, 필요한 모양이 목록에 없을 때가 많습니다. 사용자 지정으로 가서 코드를 직접 넣는 쪽이 확실합니다.
1합계 셀 하나만 선택한다
열 머리글을 누르지 않습니다. 합계가 들어 있는 그 칸만 클릭한 상태에서 다음으로 넘어갑니다.
2셀 서식 창을 연다
오른쪽 클릭 메뉴에서 셀 서식을 고르거나 단축키로 엽니다. 창이 뜨면 왼쪽 범주 목록에서 사용자 지정을 누릅니다.
3형식 칸에 코드를 친다
칸에 들어 있던 내용을 지우고 대괄호가 든 코드를 직접 칩니다. 창 위쪽 보기 자리에 결과가 미리 뜨니 확인하고 누릅니다.
미리 보기 자리를 꼭 확인합니다. 코드를 잘못 치면 창을 닫은 뒤에야 이상한 숫자를 발견하게 되는데, 그 자리에서 결과를 미리 보면 대괄호가 제대로 먹었는지 바로 알 수 있습니다.
초까지 보여야 하면 [h]:mm:ss를 씁니다. 대괄호는 맨 앞 단위에만 붙이는 것이고, 분 쪽에 붙이면 전체를 분으로 환산해 보여 줍니다.
사용자 지정에 한 번 넣은 코드는 그 파일 안에 남습니다. 다음에 다른 셀에 같은 서식을 줄 때는 목록 아래쪽에서 고르면 되고, 다른 파일에서는 다시 쳐 넣어야 합니다.
코드를 외우기 어려우면 서식을 적용한 셀 하나를 복사해 두고 서식 복사 단추로 옮기는 방법도 있습니다. 값은 건드리지 않고 표시 형식만 따라가므로 합계 셀이 여러 개일 때 편합니다.
내 데이터가 이런 모양일 때
서식을 바꿨는데도 안 맞는 경우는 값 쪽 문제입니다
실무 표는 예제처럼 깨끗하지 않습니다. 아래 네 가지가 시간 합계를 틀어지게 만드는 대표적인 모양이고, 각각 고치는 자리가 다릅니다.
| 데이터 모양 | 증상 | 고치는 자리 |
|---|---|---|
| 시간이 글자로 들어와 있다 | 합계가 0으로 나온다 | 왼쪽으로 붙어 있는지 보고, 텍스트 나누기로 한 번 통과시켜 숫자로 바꿉니다 |
| 퇴근이 자정을 넘긴 날이 있다 | 그 날만 음수가 된다 | 퇴근에서 출근을 뺀 값에 1을 더해 하루를 채워 줍니다 |
| 빈 줄이 중간에 섞여 있다 | 합계 범위가 반만 잡힌다 | 범위를 손으로 끝까지 잡거나 표로 만들어 범위가 따라오게 합니다 |
| 45분을 0.75로 적어 두었다 | 합계가 이상하게 작다 | 그 숫자를 24로 나눠 시간값으로 바꾼 다음 더합니다 |
넷째 줄은 반대 방향의 실수입니다. 사람이 읽기 편하라고 45분을 0.75로 적어 두면 엑셀은 그것을 0.75일, 즉 열여덟 시간으로 읽습니다. 숫자로 적힌 시간은 24로 나눠 시간값으로 돌려 놓고 더해야 합니다.
둘째 줄도 근태 표에서 자주 나옵니다. 야간 근무가 있는 곳이면 퇴근 시각이 출근보다 작은 날이 생기고, 그 날만 음수로 떨어지면서 합계 전체가 줄어듭니다. 합계가 예상보다 작을 때는 개별 행 중에 마이너스가 있는지부터 훑어보는 편이 빠릅니다.
지금 해보세요 — 확인
자기 표에서 시간 열을 한 번 클릭해 값이 어느 쪽으로 붙는지부터 봅니다. 오른쪽에 붙어 있으면 숫자라 서식만 고치면 되고, 왼쪽에 붙어 있으면 값을 먼저 숫자로 바꿔야 합니다. 이 한 번의 확인이 나머지 세 줄까지 걸러 줍니다.
첫째 줄이 가장 흔합니다. 근태 시스템에서 내려받은 표는 시간이 글자로 들어오는 경우가 많고, 그때는 서식을 아무리 바꿔도 합계가 0에서 움직이지 않습니다.
숫자로 받아야 할 때
급여 계산에 넘길 값은 시:분이 아니라 숫자여야 합니다
58:30은 사람이 읽는 표기이고, 시급을 곱하려면 58.5라는 숫자가 필요합니다. 시간값에 24를 곱하면 시간 단위 숫자가 나옵니다.
이 셀은 서식을 일반이나 숫자로 두어야 합니다. 시간 서식이 남아 있으면 58.5라는 숫자를 다시 시간으로 읽어 엉뚱한 값을 보여 줍니다.
지금 해보세요 — 확인
곱하기 24를 넣은 셀이 58.5로 나오는지 봅니다. 여기서 시간 모양이 뜬다면 서식이 아직 시간이라는 뜻이니, 그 셀만 일반으로 되돌린 다음 다시 봅니다.
분 단위가 필요하면 서식 쪽으로 처리하는 방법도 있습니다. 코드를 [m]으로 주면 같은 값이 3510이라는 총 분으로 표시됩니다. 이쪽은 표시만 바뀌는 것이라 계산에 쓰려면 곱하기 쪽으로 가야 합니다.
반올림을 어디서 할지도 미리 정해 둡니다. 곱하기 24를 한 값은 58.5처럼 떨어지기도 하지만 분 단위가 애매하면 소수점이 길게 남고, 그 상태로 시급을 곱하면 원 단위에서 어긋납니다. 급여로 넘길 값이면 소수점 두 자리쯤에서 정리해 두는 편이 낫습니다.
표시용 셀과 계산용 셀을 나란히 두는 것도 방법입니다. 한 칸에는 대괄호 서식으로 58:30을, 옆 칸에는 곱하기 24로 58.5를 두면 보는 사람도 편하고 뒤 계산으로 넘길 값도 준비됩니다.
그래도 안 될 때
서식을 바꿨는데 화면이 그대로면 셋 중 하나입니다
코드를 제대로 넣었는데도 숫자가 안 바뀌는 경우가 있습니다. 대개는 서식이 안 먹은 게 아니라 다른 것이 걸려 있는 상황입니다.
1셀 안의 값이 글자다
값이 왼쪽으로 붙어 있으면 서식은 아무 일도 하지 않습니다. 숫자로 바꾸는 작업이 먼저입니다.
2대괄호를 반각으로 안 쳤다
한글 입력 상태에서 친 전각 괄호는 서식 코드로 인식되지 않습니다. 영문 입력으로 바꿔 다시 칩니다.
3조건부 서식이 덮고 있다
그 셀에 걸린 조건부 서식이 표시 형식까지 들고 있으면 나중에 넣은 서식보다 우선합니다. 규칙 관리에서 해당 규칙을 잠시 꺼 보고 확인합니다.
셋째 줄은 물려받은 파일에서 자주 걸립니다. 앞사람이 걸어 둔 규칙이 남아 있는데 규칙 목록을 열어 보지 않으면 보이지 않아서, 서식이 안 먹는 이유를 한참 다른 데서 찾게 됩니다.
넷째 가능성도 있습니다. 합계 셀이 아니라 그 위 어느 행의 값이 비어 있는데 서식만 시간으로 잡혀 있으면 화면에는 0:00이 보이지만 실제로는 빈 칸이라, 범위를 끝까지 잡았는데도 기대한 숫자보다 작게 나옵니다. 의심스러운 행을 눌러 수식 입력줄이 비어 있는지 보면 금방 갈립니다.
셋 다 아니면 값 자체를 의심합니다. 합계 셀을 눌러 수식 입력줄을 보고, 범위가 표 끝까지 잡혀 있는지부터 확인하는 편이 빠릅니다.
둘째 줄은 눈으로 구분이 안 됩니다. 전각 괄호와 반각 괄호는 화면에서 폭만 조금 다를 뿐이라, 서식 코드가 안 먹는데 이유를 모르겠으면 칸을 통째로 지우고 영문 입력 상태에서 새로 치는 쪽이 빠릅니다.
자주 묻는 질문
본문에서 다루지 않은 것만 모았습니다
Q.대괄호를 분 쪽에 붙이면 어떻게 되나요
A.전체를 분으로 환산해 보여 줍니다. 같은 값이 3510으로 뜨는데, 시간과 분을 나눠 보고 싶은 것이 아니라 총 분이 필요할 때 쓰는 모양입니다.
Q.시간이 아니라 일수로 보고 싶으면 어떻게 하나요
A.합계 값 자체가 이미 일 단위입니다. 서식을 일반으로 바꾸면 2.4375가 그대로 보이고, 하루 여덟 시간 기준으로 며칠인지가 필요하면 곱하기 24를 한 값을 8로 나눕니다.
Q.대괄호 서식이 든 셀을 다른 파일에 붙이면 유지되나요
A.서식까지 함께 붙이면 따라갑니다. 값만 붙이기로 옮기면 서식은 받는 쪽 셀의 것이 적용되므로 다시 씌워야 합니다.
Q.피벗 테이블의 시간 합계도 같은 문제가 생기나요
A.생깁니다. 다만 피벗은 셀 서식을 직접 바꿔도 새로 고치면 되돌아가므로, 값 필드 설정 안의 표시 형식에서 같은 코드를 넣어야 유지됩니다.
Q.합계가 마이너스로 나오면 어떻게 표시되나요
A.시간 서식으로는 음수를 보여 주지 못해 우물 정자가 줄지어 나옵니다. 값 자체는 살아 있으니 서식을 일반으로 바꾸면 숫자로 확인할 수 있고, 음수가 생긴 행을 찾아 계산을 고치는 쪽이 근본 해결입니다.
Q.구글 시트에서도 대괄호가 되나요
A.맞춤 숫자 형식에 같은 코드를 넣으면 동작합니다. 메뉴 위치가 다르니 서식 메뉴 안쪽에서 숫자 쪽을 열어 맞춤 항목을 찾습니다.
처음부터 다시 볼 순서
위에서부터 짚으면 어디서 막혔는지가 드러납니다
아래 그림은 같은 시트에서 어느 셀에 어떤 서식이 들어가야 하는지를 한 장에 모은 것입니다. 자기 표와 자리를 맞춰 본 다음 체크리스트로 내려갑니다.
정리 — 위에서부터 체크하세요
□ 개별 시간 값이 오른쪽으로 붙어 있다
□ 합계 범위가 표 끝까지 잡혀 있다
□ 합계 셀 하나만 선택하고 서식을 바꿨다
□ 대괄호를 영문 입력으로 쳤다
□ 원본 시각 셀에는 대괄호를 씌우지 않았다
□ 숫자로 넘길 셀은 곱하기 24에 일반 서식을 두었다
□ 자정을 넘긴 날이 있으면 그 날만 따로 계산했다
체크를 마치고도 값이 미심쩍으면 합계 셀 옆에 곱하기 24를 넣은 칸을 하나 두고 두 값을 같이 봅니다. 58:30과 58.5가 나란히 있으면 서로를 검산해 주니, 어느 한쪽만 이상할 때 바로 눈에 띕니다.
표를 새로 만들 때 합계 셀에 서식을 먼저 씌워 두는 습관을 들이면 이 글을 다시 열 일이 없습니다. 값이 채워지기 전에 서식부터 잡아 두면 나중에 숫자가 이상해 보이는 단계 자체가 생기지 않습니다.
체크 항목이 많아 보여도 실제로 손이 가는 곳은 합계 셀 하나입니다. 나머지는 값이 제대로 들어와 있는지 보는 것이라, 한 번 정리해 둔 표에서는 다시 볼 일이 거의 없습니다.
여기까지 맞는데도 숫자가 어긋나면 값이 소수점 아래에서 미세하게 다른 경우가 있습니다. 시간은 1을 24와 60으로 나눈 값이라 딱 떨어지지 않는 자리가 생기는데, 합계를 분 단위로 반올림해 두면 그런 오차가 화면에 드러나지 않습니다.
#엑셀 #시간합계 #셀서식 #근무시간 #엑셀함수
