이번 포스팅에서는 엑셀(Excel)로 근무 시간, 프로젝트 소요 시간 등을 계산할 때 많은 사용자들이 겪는 ’24시간’의 함정을 피하고, 정확한 엑셀 시간 계산 및 합계를 구하는 방법에 대해 1분 만에 해결할 수 있도록 자세히 정리해보았습니다.
참고로 아래 글은 아래 테스트 날짜와 기기, 앱 버전에서 작동 여부를 직접 확인 완료 하였습니다.
테스트 날짜 : 25년 10월 25일
윈도우 버전 : Windows 11 Home / 25H2
사용기기 : Microsoft Surface Pro 11th Edition
프로그램명/버전 : Microsoft 365 / Excel / 버전 2508
엑셀은 데이터 처리와 분석에 있어 현존하는 가장 강력하고 효율적인 도구입니다. 특히 출퇴근 기록, 급여 계산, 프로젝트 투입 시간 관리 등 ‘시간’ 데이터를 다룰 때 엑셀의 편리함은 빛을 발합니다. 하지만 많은 사용자들이 엑셀 시간 계산, 그중에서도 시간의 합계를 구하는 과정에서 큰 혼란을 겪습니다.
신입사원 시절, 프로젝트에 투입된 총 시간을 계산하는데, 분명 며칠 밤을 샌 것 같은데 합계가 고작 몇 시간으로 나와 당황했던 기억이 있습니다. 21:00와 05:00를 더했는데 결과가 26:00이 아닌 02:00으로 표시되는 마법, 바로 엑셀 시간 계산의 가장 큰 함정인 ’24시간 리셋’ 문제였습니다.
이러한 문제가 발생하는 이유는 엑셀이 시간을 인식하고 표시하는 독특한 방식 때문입니다. 엑셀은 시간을 하루(24시간)를 ‘1’로 보는 소수점 숫자로 저장합니다. 예를 들어, ‘정오 12:00’는 하루의 절반이므로 숫자 ‘0.5’로, ‘오전 6:00’는 ‘0.25’로 인식합니다. 그리고 기본 시간 표시 형식(h:mm)은 24시간을 넘어가면 다시 0시부터 시작하도록 설정되어 있습니다. 따라서 26시간은 하루 하고도 2시간이므로, ‘2:00’으로 표시되는 것입니다.
따라서 정확한 엑셀 시간 계산을 위해서는, 이 기본 원리를 이해하고 ‘셀 서식’을 자유자재로 다룰 줄 아는 것이 무엇보다 중요합니다. 이번 글에서는 단순 뺄셈부터, 24시간을 초과하는 누적 합계를 정확히 계산하는 핵심 비법까지, 엑셀 시간 계산의 모든 것을 파헤쳐 보겠습니다.
앞선 포스팅에서 정리한 엑셀 관련 글은 아래와 같습니다.
자동 만년 Excel 엑셀 달력 만들기 방법(함수와 조건부 서식 활용)
[Excel] 엑셀 표 그대로 복사 붙여넣기 방법 2가지
엑셀 함수 안됨 계산 인식 오류 해결 방법
엑셀 필터 오류, 정렬 안됨 오류 10초 해결
엑셀 페이지 표시 없애기, 보이게 설정 (엑셀 워터마크 없애기)
[Excel 꿀팁] 엑셀 증감율 계산 및 시각화 방법
엑셀 셀서식이란? 엑셀 자동 셀서식 지정의 함정
엑셀에서 매우 유용한 기능이지만 엑셀 시간 계산이나 날짜 계산을 할 때 사용자에게 함정에 빠지게 하는 기능이 바로 “엑셀 자동 셀서식” 입니다.
대부분 이 부분 때문에 사용이 매우 손쉬워지지만 반면에 엑셀 시간 더하기, 빼기 또는 엑셀 날짜 더하기 빼기를 진행할 때 헤매게 됩니다.
이 부분만 이해하면 엑셀 시간 계산이 매우 쉬워집니다.
우선 아래 이미지를 보면 엑셀 셀에
“12:34″를 입력하면 “12:34″로 표현되기는 하지만 해당 셀을 눌러보면 상단에 12:34:00 PM 으로 시간 셀 서식으로 자동 변환된 것을 알 수 있습니다.
날짜도 마찬가지 있는데요, “1/23″을 입력하면 “1월 23일”로 자동 변경되고 해당 셀을 눌러보면 상단에 2022-01-23로 날짜 서식으로 바뀐 것을 알 수 있습니다.
즉, 위와 같이 셀 서식이 바뀌게 되면 엑셀 시간 계산이나 엑셀 날짜 계산이 매우 편리해지기는 하는데요,
문제는 엑셀 시간, 날짜의 표시 형식에 따라서 엑셀 시간, 날짜의 더하기/빼기 등을 할 때 골치아픈 일이 생긴다는 점 입니다.
가령 엑셀 시간의 기본 표시 형식은 (24시간):(60분) 과 같은 형태로 앞에 시간은 24시간까지 나타나기 때문에 엑셀 시간 더하기를 할 경우 24시간을 넘기게 되면 기본 표시형식으로는 엑셀 시간 계산이 이상하게 나타나게 됩니다.
가령, 엑셀 기본 시간 셀 서식에서 21:00와 5:00를 더하게 되면, (21+5) : 00 이므로 26:00 으로 기대하는 경우도 있는데 실제로는 24시간이 넘어간 시간을 제외하여 2:00 으로 나타나게 됩니다.
엑셀 셀 서식 변경 방법
이러한 문제는 해당 셀을 선택한 다음 마우스 오른쪽 버튼을 누르면 나타나는 팝업창에서 “셀 서식”을 선택하거나 상단에 있는 “표시 형식” 부분 버튼을 누르면 나타나는 셀서식 > 표시형식을 통해 변경을 해주어야 합니다.
그럼 앞서 정리한 엑셀 셀서식 관련 내용을 참고로 하여 다음 예시에서는 엑셀 시간 계산 방법을 정리해보겠습니다.
엑셀 시간 계산 방법 예시
이번에는 엑셀 시간 계산 예시로 엑셀 시간 기와 엑셀 시간 더하기 방법을 정리해보겠습니다.
1. 엑셀 시간 빼기
두 시간 사이의 간격을 구하는 ‘엑셀 시간 빼기’는 비교적 간단하고 문제가 거의 발생하지 않습니다.
① 종료 시간 – 시작 시간 수식 입력 근무 시간이나 소요 시간을 계산하고 싶은 셀을 선택한 후, =종료시간_셀 – 시작시간_셀 형식으로 수식을 입력합니다. 예를 들어, 종료 시간이 D2셀에, 시작 시간이 C2셀에 있다면, 수식은 =D2-C2 가 됩니다.
② 채우기 핸들로 수식 일괄 적용 계산이 완료된 셀의 오른쪽 하단 모서리에 있는 작은 네모(채우기 핸들)를 마우스로 잡고 아래로 쭉 끌어내리거나 더블클릭하면, 아래의 모든 행에 동일한 계산식이 자동으로 적용됩니다.
③ (개인 노하우) 시간 단위를 시급 계산용 숫자로 변환하기 (*24) 위 계산 결과(예: 8:30)는 여전히 ‘시간 형식’의 데이터입니다. 만약 이 근무 시간에 시급을 곱해 급여를 계산하고 싶다면, 이 시간 값을 숫자로 바꿔야 합니다.
- 핵심 비법: 시간 값에
24를 곱해주면 됩니다. - 수식: = (D2-C2) * 24
- 결과: 8:30 (시간) → 8.5 (숫자) 이제 이 8.5라는 숫자 값에 시급을 곱하면 정확한 급여를 계산할 수 있습니다. 이것이 실무에서 엑셀 시간 계산을 활용하는 핵심 노하우입니다.
예시를 들어 아래와 같이 상세하게 살펴보겠습니다. 가령 아래와 같이 D8셀과 D7셀에 있는 시간 차이를 계산한다면 아래와 같이 “=D8-D7” 을 입력해주면 됩니다.
물론, 시간 차이를 계산하고자 한다면 해당 셀의 표시 형식을 시간 형식으로 지정해주어야 합니다.
참고로 나머지 행들에 대한 시간 차이를 그대로 하려면 해당 셀의 우측 하단을 잡고 아래로 끌어내리면 계산식을 다른 셀에도 적용할 수 있습니다.
그러면 아래와 같이 모든 행에 시간 차이가 입력된 것을 확인할 수 있습니다.
2. 엑셀 시간 더하기
엑셀 시간 빼기와 달리 엑셀 시간 더하기는 헤매는 경우가 많습니다. 앞서 설명한 바와 같이 시간의 합이 24시간을 넘어가는 경우 24시간을 뺀 값이 표시되기 때문입니다. 가령 아래 예시의 경우, F열에 시간 차이에 대해 누적으로 시간 합계를 더한다고 할 때에는 더욱 문제가 되는데요,
여러 시간 값의 합계가 24시간을 초과할 때 아래의 셀 서식 변경 방법을 반드시 사용해야 합니다.
우선 가령 아래와 같이 E열에 있는 시간 차이의 누적합을 F열에 표시하고자 한다면, F9셀은 F8셀과 E9셀의 합, 즉 “=F8+E9″를 입력해주면 됩니다.
아래에 있는 행들에 대한 시간차이를 누적해서 합계를 표시하고자 한다면 F9셀 우측 하단을 잡고 아래로 내려서 끌면 다른 셀에도 해당 계산식이 적용 됩니다.
문제는 아래 이미지와 같이 누적된 계산식의 값들 중에 24시간을 넘어서 표시된 값에 대해서는 모두 24시간을 뺀 값이 입력된다는 것 입니다.
이러한 경우 셀서식 변경이 필요합니다.
앞선 세션에서 설명한 바와 같이 해당 셀을 선택한 다음 마우스 오른쪽 버튼을 누르면 나타나는 팝업창에서 “셀 서식”을 선택하거나 상단에 있는 “표시 형식” 부분 버튼을 누르면 나타나는 셀서식 > 표시형식에서 변경해주어야 합니다.
현재는 표시형식이 시간으로 되어 있는데 이 부분을 “사용자 지정” 부분에서 [h]:mm:ss를 선택해줍니다.
그리고 상단에 형식 부분에서 ss부분을 삭제하고 [h]:mm 으로 저장해줍니다.
위와 같이 설정을 하면 24시간이 넘은 값에 대해서도 누적해서 아래와 같이 정상적으로 엑셀 시간 합계가 표시됩니다.
참고로 엑셀 시간 더하기 빼기에 대해서는 마이크로소프트 지원 페이지 여기 링크에서도 간단하게 안내하고 있습니다.
엑셀 시간 계산 관련 자주 묻는 질문 (FAQ)
Q1: 시간 차이를 계산했는데 ##### 오류가 떠요. 왜 그런가요?
A1: 두 가지 가능성이 있습니다. 첫째, 종료 시간이 시작 시간보다 빨라서 결과값이 ‘음수 시간’이 된 경우입니다. 엑셀은 기본적으로 음수 시간을 표시하지 못하고 ##### 오류를 냅니다. 둘째, 셀 너비가 너무 좁아서 결과값을 다 보여주지 못하는 경우입니다. 셀 너비를 넓혀보거나, 날짜와 시간을 함께 입력하여 계산해야 합니다.
Q2: 야간 근무처럼 자정을 넘기는 시간 계산은 어떻게 하나요? (예: 22:00 ~ 06:00)
A2: 종료 시간이 시작 시간보다 작을 때 발생하는 문제를 해결하는 수식입니다. 가장 간단하고 확실한 수식은 =(종료시간-시작시간) + (종료시간<시작시간) 입니다. (종료시간<시작시간) 부분은 조건이 참(True)일 때 숫자 1(24시간)을 더해주고, 거짓(False)이면 0을 더해주는 원리를 이용한 것입니다. 이 수식 하나면 모든 엑셀 시간 계산이 가능합니다.
Q3: 셀 서식을 [h]:mm으로 바꿨는데도 합계가 이상해요.
A3: 합계를 구하려는 데이터 중에 일부가 시간 형식이 아닌 ‘텍스트’ 형식으로 입력되었을 가능성이 높습니다. 셀 왼쪽 상단에 작은 녹색 삼각형 표시가 있는지 확인해 보세요. 데이터 범위를 모두 선택한 후, 데이터 형식을 ‘시간’으로 변경하고 다시 계산해야 합니다.
Q4: 시간 합계를 시급 계산에 사용하고 싶어요.
A4: 앞서 설명한 * 24 비법을 사용하시면 됩니다. [h]:mm 서식으로 구한 총 누적 시간(예: 150:30)이 있는 셀에 * 24를 곱하고, 셀 서식을 ‘숫자’ 또는 ‘일반’으로 변경하면 150.5 라는 급여 계산용 소수점 숫자를 얻을 수 있습니다.
Q5: 셀에 ’30:00’이라고 직접 입력할 수 없나요?
A5: 그냥 입력하면 엑셀이 시간으로 인식하지 못합니다. 하지만 셀 서식을 먼저 [h]:mm으로 변경해 둔 상태라면, ’30:00’이라고 입력해도 30시간으로 정상적으로 인식하고 계산에 활용할 수 있습니다.
요약 정리
- 엑셀 시간의 비밀: 엑셀은 시간을 하루(24시간) = 숫자
1로 인식하는 소수점 체계를 사용합니다. - 엑셀 시간 빼기 (소요 시간):
- =종료시간 – 시작시간
- 급여 계산 등 숫자로 변환이 필요할 땐 =(종료시간 – 시작시간) * 24
- 엑셀 시간 더하기 (누적 합계 > 24시간):
- (문제) 기본 서식에서는 합계가 24시간을 넘으면 나머지만 표시됨 (예: 26시간 → 2시간)
- (해결) 합계 셀 선택 → Ctrl + 1 (셀 서식) → 사용자 지정 → 형식 입력란에 [h]:mm 입력.
- 자정 넘는 시간 계산:
- =(종료시간 – 시작시간) + (종료시간 < 시작시간) 수식 활용.
’24시간 리셋’ 문제의 원인과 해결책인 [h]:mm 셀 서식, 그리고 시간 값을 숫자로 바꾸는 * 24 이 두 가지만 확실히 기억한다면, 앞으로 그 어떤 엑셀 시간 계산 작업도 자신 있게 해결할 수 있을 것입니다.
이번 포스팅에서는 엑셀 시간 계산을 중심으로 엑셀 시간 더하기, 빼기 또는 합계 처리 방법에 대해 알아보았습니다. 먼저 문제의 원인이 되는 자동 셀서식 지정에 대해 알아보고, 엑셀 시간 계산 예시를 만들어 엑셀 시간 더하기와 빼기를 통해 자주 어려움을 겪는 셀서식 부분에 대해 정리해보았습니다.
앞선 포스팅에서 정리한 엑셀 사용법 및 노하우 관련 글은 아래와 같습니다.
엑셀 줄바꿈, 엑셀 텍스트 줄바꾸기 5초 끝내기(단축키 및 자동설정)
작성자 : Sean Woo
[전공] HCI(인간-컴퓨터 상호작용)·UX(사용자 경험 연구)
[업무] 신기술 기반 사업 전략·기술 기획·디지털 트랜스포메이션 업무
ITmanual.net에서는 실생활, 업무를 진행하면서 느꼈던,
스마트폰(아이폰·갤럭시)·운영체제·각종 앱/프로그램·IT기기 활용 노하우를 실생활 중심 IT 매뉴얼로 정리합니다.