[엑셀 문답] 날짜와 시간을 한 셀에 합치는 방법

엑셀을 사용하다보면 날짜와 시간을 다른 셀에 입력하였거나 다른 셀에 작성한 날짜와 시간을 하나로 결합하고 싶을 때가 생깁니다. 이때 여러분은 어떤 방법을 사용하시나요? 이 질문과 해결방법을 제시하는 답변입니다.

**님의 질문입니다

제목 : 날짜와 시간을 한 셀에 합치는 방법을 알려주세요.
내용 :
.....
엑셀을 이용해 회사의 작업날짜와 시간을 관리하고 있습니다.

[그림1] 엑셀 날짜와 시간을 합치는 방법 - 질문 예제

그림에서 질문드렸지만 두가지의 질문을 드립니다.

1. 분리되어 있는 날짜와 시간을 하나의 셀에  합쳐서 표시할 수 있을까요?
2. 날짜와 시간값이 함께 표시되는 셀과 셀에서 시간의 차이를 구하고 싶습니다.
....

마늘빵의 답변입니다

.....
질문의 형태로 예를 다시 작성하여 설명드립니다. 이하의 설명은 [그림2] 를 따릅니다.

[그림2] 엑셀 날짜와 시간을 합치는 방법

먼저 첫번째 답변입니다.

D4 는 =A4+B4 의 수식을 사용하시면 날짜와 시간이 결합된 값이 셀의 값이 됩니다. 어렵게 생각하실 것 없습니다.

이렇게 시간과 날짜의 더하기가 가능한 것은 시간도 날짜도 엑셀에서는 숫자값으로 인식되기 때문입니다. 이 블로그에서 자주 보셨겠지만 시간과 날짜는 일련번호를 가지고 있습니다.

날짜 일련번호는 1900년 1월 1일을 기준으로 사용한 날짜까지의 경과일수를 말합니다. A4 셀의 날짜 일련번호는 42511 입니다. 이 만큼의 날짜가 경과하였다는 것이지요. A5 셀의 날짜 일련번호도 동일한 날짜이기에 같습니다.

시간 일련번호는 오늘 오전 12:00:00 를 0 으로 보고 내일 오전 12:00:00 를 1로 보고 현재시각의 위치를 표현하는 것입니다. 오후 12:00:00 라면 0.5가 시간 일련번호가 되는 것입니다. 이렇게 B4, B5 의 시간 일련번호가 F4, F5 처럼 구해집니다.

이제 시간과 날짜가 더해지는 원리를 아시겠지요. D4 = 날짜일련번호 + 시간일련번호 로 구해진 것입니다.

여러분이 이 날짜와 시간을 합하기 위하여 더하기(+) 대신 문자열 더하기 연산자인 앰퍼샌드(&) 를 이용하였다면 이는 문자열로 날짜일련번호와 시간일련번호가 더해지는 것입니다. E4와 F4 를 & 로 더하여 구한다면 425110.515972 과 같은 결과가 나옵니다. 생각한 더하기의 결과와 다르지요. 당연히 정확한 날짜와 시간의 합치기가 되지 않는 것입니다. 이점 유의하십시오.

날짜와 시간은 숫자값이기에 더하기 연산자(+) 를 사용하여 합한다.

두번째 답변입니다.

두번째 질문의 경우는 날짜들의 차이에 의한 전체 시간 계산후 시간값을 요구하는 것인지 단순히 두 셀에서 시간 단위의 차이만을 요구하는지 알 수 없네요. 하여 후자의 경우로 답변드립니다.

함수로 서식을 지정하는 엑셀 TEXT 함수 :
http://secstart.tistory.com/581

D7에 셀 포인터를 놓고 =TEXT(D5-D4, "H") 의 수식을 입력하시면 D5 - D4 의 결과인 0.129861111 의 날짜시간 일련번호에서 시간 부분만을 표시합니다. D5 셀과 D4 셀은 3:07 분의 차이를 보입니다. 여기서 시간만을 표시하는 것이지요. 3이 표시됩니다.

.....

**님의 추가 질문입니다

.....
와!! 정말 감사합니다!!

저 죄송한데요 ㅠㅠ 두번째 질문에 날짜들의 차이에 의한 전체 시간 계산 후에 시간값을 구하는것이 필요해서요.
그리고 분단위는 무조건 단 1분이라도 표시된다면 올림이 되어 시간에 포함이 되어야 하거든요.

=TEXT(D5-D4, "DD") * 24 + TEXT(D5-D4, "HH") + TEXT(D5-D4, "MM")   >>   오류

이렇게 해도 괜찮을까요?? 답이 맞게 나오긴 했습니다만.. ㅠㅠ 확신이 서질 않습니다 ㅠㅠ
.....

마늘빵의 추가 답변입니다

.....
원하는 자리에서 올림을 하는 엑셀 ROUNDUP 함수 :
http://secstart.tistory.com/150

날짜의 차이도 시간으로 계산하고 분단위를 무조건 시간으로 올림하신다면 다음의 수식을 사용하시면 됩니다.

=ROUNDUP((D5-D4) * 24, 0)
 
=D5-D4 의 결과는 0.129861 입니다. 해당 셀서식에 따라 표시 되는 결과는 달라질 수 있습니다. D4 와 D5 셀은 날짜와 시간이 함께 표시되고 있습니다. 날짜도 시간 계산에 포함하기를 원하시기에 1일은 24시간, 24를 곱하는 것입니다.

이제 모든 수치는 시간단위로 변환되었습니다.

마지막 과정, 분단위 올림입니다. 올림을 위한 엑셀 ROUNDUP 함수를 사용하여 소수 단위인 분단위를 올림하여 시간으로 표시합니다.

한가지 주의 하실것은 위의 수식은 D5가 D4 보다 이후의 날짜일때만 바른 답을 반환합니다. 확인하시기 바랍니다.

1) =D5-D4   >>  0.129861111  >>  두셀의 날짜 시간 차이, 이 과정에서 소수이하는 시간 일련번호
2) =(D5-D4)*24  >>  3.116666667  >>  시간 변환, 이 과정에서 소수이하는 분단위
3) =ROUNDUP((D5-D4)*24, 0)  >>  4  >> 2) 과정의 3 시간 + 분단위 올림 1

이상의 과정으로 답변 요약됩니다.

질문자가 작성한 수식은 오류가 있습니다.
마지막 TEXT 함수식에 사용된 MM 은 **님의 생각처럼 시간단위인 분을 표시하는 서식이 아닙니다. 이때의 MM 은 월을 표시하는 날짜 서식입니다. 분을 표시하는 MM 은 시간의 뒤에, 초 앞에서 함께 사용되어야 제 기능을 합니다. 확인하시기 바랍니다.
.....

엑셀 기본적인 연산자와 기호 : http://secstart.tistory.com/565

마늘빵.