시간 표시 방법 2 - 시간 표시에 쉼표 넣기

1. 문제

시간표시2.xlsx

0.01MB

 

 

시간표시1.xlsx에서 B2셀의 값이 B1셀을 복사한 것이었는데,

B1셀에만 값을 입력하면 B2셀의 값도 바뀌도록 =b1으로 수정했습니다.

 

 

그리고, B1셀의 날짜를 10/4로 수정하면

2,954로 표시됐으면 좋겠는데 2954로 표시됩니다.

 

이것에 대해 해결 방법을 탐구해 보도록 하겠습니다.

 

2. 시간 표시에 천 단위마다 쉼표 넣기

가. 시간 차이 구하기

엑셀에서 날짜와 시간을 빼면 알아서 날짜와 시간 형식을 바꿔주는데,

천 단위마다 쉼표를 넣기 위해서는 시간을 수식으로 구해야 합니다.

 

하루는 24시간이므로 큰 날짜와 시간에서 작은 날짜와 시간을 뺀 다음 24를 곱하면 시간 차이가 됩니다.

b2셀의 표시형식을 숫자로 바꿔보면

 

45570으로 표시되므로, 

 

소수점이하 숫자를 표시하기 위해 자릿수 늘림 명령을 다섯 번 누르면

 

45569.62153이 되고,

 

소수점이하 부분만 24를 곱해보면 14.91672가 나오는데, 14가 시간입니다. 

 

 

위와 같이 24를 곱하는 것이 중요합니다.

그리고, 시간 차이를 구한 다음 정수부분만 취해야 합니다.

 

B2셀의 서식을 원래대로 돌리기 위해 B1셀에 커서를 두고, 서식 복사 명령을 누른 후 B2셀을 클릭합니다.

 

 

위의 내용을 종합해 볼 때 수식은 =int((b2-a2)*24)가 됩니다.

 

구한 값이 2954로 [h]:mm으로 구한 시간인 2954와 같습니다.

 

이제 Text 함수를 이용해 천 단위마다 쉼표를 넣으면 됩니다.

 

나. 시간 차에 천 단위마다 쉼표 넣기

수식은 =text(int((b2-a2)*24),"#,##0")

위 수식에서 #은 숫자를 의미하며 0인 경우는 표시하지 않는데,

0은 숫자가 0일 때에도 0을 표시하라는 차이가 있습니다.

 

숫자 뒤에 "시간"을 붙이려면

& "시간"을 추가하거나, 

 

Text 함수 내에 넣으면 됩니다.

수식은 =TEXT(INT((B2-A2)*24),"#,##0시간")입니다.

 

나. 분 차이 구하기

(1) 분 차이 구하는 수식

분은 시간 차에서 위에서 정수로 구한 시간을 뺀 다음 분이므로 60을 곱하면 됩니다.

따라서, 수식은

=((B2-A2)*24-INT((B2-A2)*24))*60

이 됩니다.

5분이 맞습니다.

 

(2) Round와 Int의 차이

엑셀은 특별히 Round함수를 사용하지 않아도 표시되는 범위에 따라 자동으로 반올림해서 표시해 줍니다.

 

Microsoft 365에서는 수식을 선택한 다음 커서를 올려놓으면 수식의 결괏값 5.00000000931323이 표시되는데, 이전 버전에서는 F9키를 누르면 결괏값을 알 수 있습니다. F9키를 눌렀을 때는 원 수식으로 돌리기 위해 Esc키를 눌러야 합니다.

 

 

이제 수식의 앞에 int를 붙이면 정수부분만 가져오는데 반올림돼도 5이므로 차이가 없습니다.

=INT(((B2-A2)*24-INT((B2-A2)*24))*60)

 

Round 함수는 Mod 함수와는 달리 보이는 숫자뿐만 아니라 실제 들어 있는 숫자까지 반올림된 숫자로 바꿔주는 차이점이 있으며, 반드시 반올림할 자릿수를 지정해야 합니다.

 

원 단위 미만을 없애려면 기존 수식 앞에 round를 붙이고, 맨 뒤 괄호 앞에 ,0을 추가해야 합니다.

수식은 아래와 같습니다.

=round( (((B2-A2)*24-INT((B2-A2)*24))*60),0)

 

수식을 범위로 잡으면 그 위에 결괏값 5가 보입니다. 위에서 5.00000000931323으로 소수점미만에 숫자가 있었던 것과 다릅니다.

 

그러나, B1셀의 분을 52로 바꿔보면 2와 1로 차이가 발생하는데 2가 맞습니다. 따라서, 반올림해야 합니다.

 

신기하게 시간 차이를 구할 때는  Int 함수를 사용하는데,

분 차이를 구할 때는 Int 함수를 사용하면 안 됩니다.

 

(3) Mod 함수를 이용할 경우

(가) 구문

위 수식을 Mod 함수를 이용하면 간단히 만들 수 있습니다.

 

Mod함수는 나머지를 구해주는 함수로서,

구문은 아래와 같이 인수가 숫자와 제수로 되어 있습니다.

MOD(number, divisor)

 

9를 2로 나눌 때 9가 number(피제수), 2가 divisor(제수)가 되며,

결괏값은 1이 됩니다.

 

(2) 수식

=mod((b2-a2)*24*60,60)로

시간 차에 60을 곱해서 분으로 환산하고, 다시 60으로 나눈 후 나머지가 분이 되는 것입니다.

 

Mod를 활용하니 수식이 훨씬 깔끔해집니다.

 

(3) 분을 숫자 두 자리로 표시하기

마찬가지로 text 함수를 이용해서

=text(mod((b2-a2)*24*60,60),"00분")

이라고 하면 됩니다.

 

첫 번째 숫자가 0일 경우에도 0을 표시하도록 "00분"으로 한 것입니다.

 

3. 시간과 분 차이 결합하기

여러 가지 방식으로 시간과 분 차이를 결합할 수 있습니다.

몇 시간 몇 분이라고 하거나

중간에 콜론을 넣어서 몇 시간 : 몇 분이라고도 할 수 있습니다.

 

(수식 1)

=TEXT(INT((B2-A2)*24),"#,##0시간 ") & text(mod((b2-a2)*24*60,60),"00분")

위와는 달리 시간 다음에 공백을 표시하기 위해 "#,##0시간 "으로 바꿨습니다.

 

(수식 2)

=TEXT(INT((B2-A2)*24),"#,##0:") & TEXT(MOD((B2-A2)*24*60,60),"00")

 

첫 번째 시간 차 뒤의 "시간 "을 ":"으로 수정했습니다.

 

시간표시2(완성).xlsx

0.01MB