엑셀에서 날짜와 시간으로 근태 시간을 어떻게 계산하나요?

TL;DR

엑셀의 날짜는 1900년 1월 1일부터 매긴 일련번호이고, 시간은 하루를 기준으로 한 소수이므로 서로 빼서 기간과 근무시간을 계산할 수 있습니다. DATEVALUE와 TIMEVALUE로 날짜나 시간처럼 보이는 텍스트를 실제 값으로 변환하고, 퇴근시간에서 기준시간을 빼면 초과근무 또는 조기퇴근 여부를 판단할 수 있습니다. 기준시간과 휴일은 별도 표에 두고 셀을 참조하는 방식이 권장됩니다.

Transcript

안녕하세요 공대 나온 여자 사람들 공여사들의 김과장입니다. 오늘은 엑셀력을 바꾸는 시간 15분 여섯 번째 시간이고요. 직장인 엑셀에서도 가장 어렵다 는 날짜와 시간을 마스터 해보도록 하겠습니다. 먼저 본문을 펼쳐서 엑셀 파일을 다운로드 받아주시 고요. 자 예제가 준비되어 있습니다. 먼저 엑셀에는 날짜 모양으로 쳤 을 때 그 값을 자동으로 날짜로 바꿔주는 기능이 있어요. 기본적으로 이렇게 1/1 형태로 엔터를 치게 되면 자동으로 날짜로 바뀝니다. 그런데 여기 보면 실제 값은 2021-01-01 여기 보이는 값과 표시되는 값이 달라요. 그 이유는 우리 눈에 보이는 건 표시 형식일 뿐이기 때문이에요 . 여기를 펼쳐보면 보여지... Read More

Key Insights

• • 엑셀 날짜는 표시 형식과 무관하게 1900년 1월 1일부터 순차적으로 부여된 일련번호로 저장되므로 숫자 연산이 가능합니다.
• • 날짜 셀의 표시 형식을 숫자로 바꾸면 내부 일련번호가 나타나며, 숫자를 다시 날짜 형식으로 바꾸면 대응하는 날짜가 표시됩니다.
• • YEAR, MONTH, DAY는 실제 날짜에서 연도, 월, 일을 각각 숫자로 추출하고, DATE는 세 숫자를 계산 가능한 날짜로 결합합니다.
• • TEXT로 조합한 2021-01-02는 날짜처럼 보여도 텍스트이므로, DATEVALUE를 적용해야 실제 날짜 일련번호로 변환됩니다.
• • WEEKDAY의 반환 형식을 2로 지정하면 월요일부터 일요일까지를 1부터 7로 나타내며, TEXT 서식으로 요일명도 표시할 수 있습니다.
• • NETWORKDAYS는 시작일과 종료일 사이에서 주말을 제외한 근무일을 세며, 휴일 범위를 추가해 평일 휴일도 제외할 수 있습니다.
• • 시간의 정수 부분은 날짜를, 소수 부분은 하루 중 시간을 뜻하므로 2.5는 1900년 1월 2일 정오에 해당합니다.
• • 근태 계산은 퇴근시간에서 기준시간을 빼는 방식으로 구성하며, 회사별 기준시간을 별도 표에 두고 셀로 참조하는 것이 권장됩니다.
• 🎬 이 채널의 다른 영상:
  • 엑셀 사용자 지정 셀서식으로 보고서 숫자와 색상을 자동 표시하는 방법
  • 소기업은 어떻게 업무 매뉴얼로 신입 인수인계를 자동화할까?
  • COUNTIF·SUMIF·VLOOKUP과 피벗테이블로 업무 보고서를 만드는 방법
• 📺 이 채널: 공여사들

Questions & Answers

Q: 엑셀 날짜가 숫자로 표시되는 이유는 무엇인가요?

엑셀은 날짜를 1900년 1월 1일부터 순차적으로 매긴 일련번호로 저장하기 때문입니다. 셀에 보이는 2021년 1월 2일 같은 모양은 표시 형식일 뿐이며, 같은 셀을 숫자 형식으로 바꾸면 44198이 나타납니다. 이 구조 덕분에 날짜끼리 직접 빼서 경과 일수를 계산할 수 있습니다.

Q: 연도, 월, 일 숫자를 실제 엑셀 날짜로 어떻게 합치나요?

DATE 함수에 연도, 월, 일 값을 차례로 넣으면 계산 가능한 실제 날짜가 만들어집니다. 먼저 YEAR, MONTH, DAY 함수로 기존 날짜의 각 구성 요소를 추출할 수 있고, 추출한 값을 DATE에 다시 넣어 결합할 수도 있습니다. 결과는 표시 형식을 바꿔도 내부적으로 날짜 일련번호를 유지합니다.

Q: 날짜처럼 보이는 텍스트를 실제 날짜로 어떻게 변환하나요?

DATEVALUE 함수에 날짜 형식으로 작성된 텍스트를 넣으면 실제 날짜 일련번호로 변환할 수 있습니다. TEXT와 연결 연산자로 만든 2021-01-02는 눈에는 날짜처럼 보여도 계산 가능한 날짜가 아닙니다. DATEVALUE로 변환한 뒤 날짜 표시 형식을 적용하면 날짜 계산과 비교에 사용할 수 있습니다.

Q: 엑셀에서 특정 날짜의 요일을 어떻게 구하나요?

WEEKDAY 함수에 날짜와 반환 형식 2를 넣으면 월요일부터 일요일까지가 1부터 7의 숫자로 반환됩니다. 이 숫자를 요일 대응표에서 VLOOKUP으로 찾는 방법이 있고, TEXT 함수에 날짜와 요일 표시 서식을 지정해 토요일 같은 이름을 직접 표시하는 방법도 있습니다.

Q: 주말과 휴일을 제외한 근무일 수는 어떻게 계산하나요?

NETWORKDAYS 함수에 시작일과 종료일을 지정하면 두 날짜 사이에서 주말을 제외한 근무일 수를 계산할 수 있습니다. 추가로 휴일이 입력된 셀 범위를 holidays 인수에 넣으면 해당 휴일도 제외됩니다. 다만 이미 주말인 날짜를 휴일로 지정해도 중복해서 한 번 더 차감되지는 않습니다.

Q: 엑셀에서 오늘까지 지난 날짜 수를 어떻게 구하나요?

TODAY 함수에서 과거 날짜 셀을 빼면 해당 날짜부터 오늘까지 지난 일수를 구할 수 있습니다. 결과 셀이 날짜 형식이면 숫자가 날짜처럼 표시될 수 있으므로, 경과 일수를 확인하려면 표시 형식을 숫자로 바꿔야 합니다. TODAY는 괄호 안에 별도 인수를 넣지 않고 사용합니다.

Q: 엑셀 시간의 소수 값은 무엇을 의미하나요?

시간의 소수 값은 하루 24시간 중 경과한 비율을 의미합니다. 예를 들어 0.5는 하루의 절반인 정오이고, 0.75는 오후 6시입니다. 2.5처럼 정수와 소수가 함께 있으면 정수 2는 1900년 1월 2일을, 소수 0.5는 그날 정오를 나타냅니다.

Q: 시간처럼 보이는 텍스트를 계산 가능한 시간으로 어떻게 바꾸나요?

TIMEVALUE 함수에 시간 형식의 텍스트를 넣으면 계산 가능한 시간 일련번호로 변환됩니다. HOUR, MINUTE, SECOND 값을 TEXT와 연결 기호로 합친 문자열은 시간처럼 보여도 실제로는 텍스트입니다. TIMEVALUE를 적용한 뒤 시간 표시 형식을 선택하면 정상적인 시간 계산에 사용할 수 있습니다.

Q: 시, 분, 초를 하나의 실제 시간 값으로 어떻게 만드나요?

TIME 함수에 시, 분, 초 숫자를 순서대로 넣으면 실제 시간 값이 만들어집니다. 기존 시간에서는 HOUR, MINUTE, SECOND 함수로 각 숫자를 분리할 수 있습니다. TIME으로 다시 결합한 결과는 내부적으로 하루의 일부를 나타내는 소수이며, 셀 서식에 따라 익숙한 시간 모양으로 표시됩니다.

Q: 엑셀에서 시간을 총초와 소수 시간으로 어떻게 환산하나요?

총초는 시간에 60과 60을 곱하고, 분에 60을 곱한 뒤 초를 더해 계산합니다. 예시의 1시간 1분 11초는 3671초가 됩니다. 소수 시간은 초를 3600으로 나눈 값과 분을 60으로 나눈 값에 시간을 더하며, 같은 예시는 약 1.0197시간으로 표현됩니다.

Q: 퇴근시간으로 초과근무와 조기퇴근을 어떻게 판단하나요?

퇴근시간에서 회사의 기준 퇴근시간을 빼면 초과근무 시간을 계산할 수 있습니다. 예를 들어 오후 10시 30분에서 오후 7시를 빼면 3시간 30분이 됩니다. 계산 결과가 0보다 작으면 기준시간 전에 퇴근한 것이므로, IF 함수를 이용해 조기퇴근 여부를 구분할 수 있습니다.

Q: 근태 수식의 기준시간은 어떻게 관리하는 것이 좋은가요?

회사에서 정한 퇴근시간이나 심야근무 시작시간을 별도의 기준정보 표에 입력하고 수식에서 해당 셀을 참조하는 방식이 권장됩니다. 날짜와 시간의 내부 일련번호를 직접 기억해 수식에 쓰기는 어렵기 때문입니다. 기준이 바뀌더라도 표의 값만 수정하면 관련 계산에 반영할 수 있습니다.

Summary & Key Takeaways

• 엑셀에서 날짜와 시간은 화면에 보이는 모양과 내부 값이 다릅니다. 날짜는 1900년 1월 1일부터 순서대로 부여된 일련번호이며, 시간은 하루 중 경과 비율을 소수로 저장합니다. 예를 들어 44198은 2021년 1월 2일이고, 0.5는 하루의 절반인 정오를 뜻합니다.

• YEAR, MONTH, DAY로 날짜의 구성 요소를 추출하고 DATE로 다시 실제 날짜를 만들 수 있습니다. HOUR, MINUTE, SECOND와 TIME도 같은 방식으로 작동합니다. TEXT로 만든 2021-01-02나 01:01:11은 모양만 날짜나 시간인 텍스트이므로 DATEVALUE 또는 TIMEVALUE로 변환해야 계산할 수 있습니다.

• 날짜 차이는 두 날짜를 직접 빼서 구하고, NETWORKDAYS를 사용하면 주말을 제외한 근무일을 계산할 수 있습니다. 근태관리에서는 퇴근시간에서 기준시간을 빼 초과근무를 산출하며, 음수이면 기준시간 전 퇴근을 뜻합니다. 회사별 기준시간과 휴일은 표로 관리하고 수식에서 셀을 참조하는 편이 적합합니다.