지난 강좌에서 날짜를 일반 표시 형식으로 작성해서 잘못된 계산이 되는 경우를 확인했습니다. 날짜의 년과 달이 10진수가 아니기 때문에 발생하는 문제였죠. 처음부터 날짜 표시 형식으로 작성했다면 생기지 않을 문제였습니다. 이런 경우는 또 있습니다. 바로 시간입니다. 시간도 10진수가 아닙니다. 오전 오후는 12진수, 시, 분, 초는 60진수로 계산됩니다. 일반 표시 형식으로 작성했다면 마찬가지로 계산 시 잘못된 결과를 반환합니다.
이것 역시 가장 좋은 방법은 처음부터 시간을 작성할 때 시간 표시 형식으로 작성하는 것입니다. 하지만 별생각 없이 일반 표시 형식으로 작성했다면, 계산을 위해선 다시 시간 표시 형식으로 작성하거나 시간 표시 형식으로 반환해 줄 함수가 필요합니다. 날짜 표시 형식으로 반환해 주는 함수가 DATE였죠. 시간 표시 형식으로의 반환은 TIME 함수입니다.
위 내용의 이해가 어렵다면 아래 강좌를 확인하고 돌아와 주세요.
해결 방법은 2가지가 있었습니다. 근본적인 해결책은 역시 올바른 표시 형식으로 다시 작성하는 것이고, 그럴 수 없는 경우 함수를 사용하는 것입니다.
표시 형식을 다시 작성하는 경우 빠른 채우기를 이용하면 쉽게 바꿀 수 있었습니다. 그런데 날짜와는 달리 시간에서는 한 가지 더 고려해야 하는 사항이 있습니다. 그래서 이번엔 빠른 채우기를 이용해 시간 표시 형식으로 다시 작성하는 방법도 소개하겠습니다.
예제 파일은 고담동 주민자치센터의 주간 컴퓨터 강좌의 시간을 정리한 표입니다. 강의 시간을 알아내기 위해서 간단하게 종료 시간에서 시작 시간을 빼주면 되겠습니다.
그런데 여기서도 시작 시간과 종료 시간을 별생각 없이 앞의 2자리는 시, 뒤 2자리는 분으로 생각하고 일반의 표시 형식으로 작성(10:00 → 1000, 9:30 → 930, ...)했기 때문에 그냥 빼기로 수식을 만들면 이상한 결과가 반영됩니다.
의도한 바로는 강의 시간의 첫 자리는 시, 뒤 2자리는 분입니다. 그런데 위 결과를 확인하면 F9셀과 F12가 이상하다는 것을 알 수 있습니다. 170은 1시간 70분이란 것으로 이상한 시간이죠. 시간 19:00에서 17:30분은 빼면 1:30분이 되야 합니다. 그런데 지금 한 것은 시간이 아니라 단지 숫자 1900에서 1730을 뺀 것입니다. 당연한 결과죠.
1. 빠른 채우기를 이용해 시간 표시 형식으로 바꾸기
일단 잘못된 강의 시간을 지우고 시작 시간을 빠른 채우기를 이용해 변경해 보겠습니다.
- D5셀의 값을 확인하고 G5셀에 시간 표시 형식으로 다시 작성합니다. => 10:00
- 주의! 빠른 채우기는 연속된 표에서만 가능하다는 것 주의해 주세요. H열 이상에서는 할 수 없습니다. - 같은 방법으로 D6셀의 값을 확인하고 G6셀에 시간 표시 형식으로 다시 작성합니다. => 13:00
- 최종 G7셀에서 빠른 채우기(ctrl+e)를 진행합니다. => 자동으로 나머지 시작 시간이 시간 표시 형식으로 작성됩니다.
결과는 위 그림과 같습니다. 잘 된 것 같지만, 자세히 보면 몇 가지 문제를 확인할 수 있습니다.
930의 경우 9:30으로 반환되기를 기대했지만, 21:00으로 반환되었고, 1730의 경우 17:00으로, 900의 경우도 18:00으로 반환되었습니다. 왜일까요?
빠른 채우기의 원리를 생각하면 알 수 있습니다.
현재 빠른 채우기로 적용할 패턴의 의도는 시작 시간 앞의 문자 2개를 시로, 뒤 문자 2개를 분으로 가져오는 것입니다.
그런데 930의 경우는 앞 문자 2개를 가져오면 93이 됩니다. 93은 3일(72시간)이 지나고 21시간이 된 것입니다. 즉 무조건 앞 문자 2개를 가져오는 것이 아니라 4자리일 경우는 앞 문자 2개, 3자리일 경우는 앞 문자 1개를 가져오는 패턴이 되어야 합니다.
다음 1730의 경우도 17:00으로 표시된 이유는 시작 시간의 뒤 문자 2개를 가져오는 패턴이 아니라 숫자 0을 두 번 붙인 패턴으로 인식했기 때문입니다.
이런 문제 때문에 일반 표시 형식으로 사용한 시간의 경우 한 가지를 더 고려해 줘야 합니다.
가져올 원본이 4자리일 경우 앞 문자 2개, 3자리일 경우 앞 문자 1개를 "시"로 인식할 수 있는 패턴, 그리고 "분"은 그냥 0을 붙이는 것이 아니라 원본의 뒤 문자 2개를 가지고 오는 패턴으로 만들어야 한다는 것입니다. 즉 930을 9:30으로 한 번 더 작성하여 엑셀이 빠른 채우기의 패턴으로 인식할 수 있게 만들어줘야 한다는 것입니다.
다시 작성해 보겠습니다. 일단 잘못 작성된 G5:G12를 지웁니다.
- D5셀의 값을 확인하고 G5셀에 시간 표시 형식으로 다시 작성합니다. => 10:00
- 같은 방법으로 D6셀의 값을 확인하고 G6셀에 시간 표시 형식으로 다시 작성합니다. => 13:00
- 여기까지는 시작 시간의 앞 문자 2개를 가져와 "시"로 인식하는 패턴.
- 분은 그냥 "0" 2개를 붙이는 패턴. - 같은 방법으로 D7셀의 값을 확인하고 G7셀에 시간 표시 형식으로 다시 작성합니다. => 9:30
- 시작 시간이 3자리일 경우 앞 문자 1개를 가져와 "시"로 인식하는 패턴.
- 시작 시간의 뒤 문자 2개를 "분"으로 인식하는 패턴. - 최종 G8셀에서 빠른 채우기(ctrl+e)를 진행합니다. => 자동으로 나머지 시작 시간이 시간 표시 형식으로 작성됩니다.
결과, 시작 시간이 시간 표시 형식으로 올바르게 작성됩니다.
나름 원리를 설명했지만, 어렵다면 다음과 같이 생각하셔도 좋습니다. 데이터를 2개만 입력하고 빠른 채우기(ctrl+e)를 했을 경우 올바른 결과가 나오지 않는다면, 3개, 4개, ... 입력해서 엑셀이 올바른 빠른 채우기의 패턴을 인식하도록 한 뒤 빠른 채우기(ctrl+e)를 하면 된다는 것입니다.
종료 시간도 같은 방법으로 시간 표시 형식으로 변경하면 되고, 만들어진 값으로 시작 시간과 종료 시간을 교체(복사, 붙여 넣기)한 후 종료 시간에서 시작 시작을 빼는 단순 수식으로 강의 시간을 만들어 주면 됩니다. 이 부분은 어렵지 않을 테니, 직접 해 보세요.
2. TIME 함수로 계산하기
시작 시간과 종료 시간을 어떤 이유로 시간 표시 형식으로 교체하면 안 될 경우 TIME 함수를 사용하여 강의 시간을 반환할 수 있습니다.
TIME인수 hour, minute, second를 시간 표시 형식으로 반환
TIME(hour, minute, second)| hour | 시
- 23보다 큰 값은 24로 나눈나머지가 시간 값으로 처리. (ex, TIME(27,0,0) => 3:00 AM)
- 음수 값은 에러 발생 |
| minute | 분
- 59보다 큰 값은 시간과 분으로 변환. (ex, TIME(12,70,0) => 1:10 PM)
- 음수는 hour에서 뺀 시간으로 계산되고 hour가 0인 경우 에러 발생 |
| second | 초
- 59보다 큰 값은 시간, 분, 초로 변환. (ex. TIME(0,0,130) => TIME(0,2,10) => 12:02 AM => 0:2:10(표시 형식 h:mm:ss))
- 음수는 minute에서 뺀 시간으로 계산되고 hour와 minute가 모두 0인 경우 에러 발생
|
함수 정리는 위와 같습니다. 복잡해 보이지만 간단합니다. 시, 분, 초가 계산이 되지만 이전 일로 돌아가야 할 경우 에러가 발생한다는 것이네요.
함수 구문은 DATE 함수와 유사합니다. DATE 함수 인수가 year, month, day인 반면, TIME은 hour, minute, second가 됩니다. 두 함수가 모두 3개의 인수이고 기능상 DATE는 날짜를, TIME은 시간을 반환한다는 점이 다릅니다.
사실 날짜와 시간의 실제 값은 숫자입니다. 그냥 보기에 날짜와 시간으로 보이는 것뿐이죠. 기초 강좌 편에서 알아본 바 있습니다. 이 부분을 모르신다면 개념 정리를 위해 아래 강좌를 꼭 확인해 주세요.
TIME 함수 사용법을 알았으니, 위 예제는 TIME 함수를 적용한 종료 시간에서 TIME 함수를 적용한 시작 시작을 빼면 강의 시간이 반환될 것을 예측할 수 있습니다.
그런데 DATE 함수처럼 TIME 함수도 원본의 종료 시간과 시작 시간을 글자 수로 분리하고 추출해야 합니다. LEFT, MID, RIGHT 함수 중 선택해서 사용해야 한다는 얘기죠. LEFT, MID, RIGHT 함수를 모르신다면 아래 강좌를 참고해 주세요.
그럼 TIME 함수의 인수들을 어떻게 설정해야 할지 생각해 보겠습니다.
일단 세 번째 인수 second는 현재 종료 시간과 시작 시간에 없습니다. 그냥 0으로 설정하면 되겠네요.(second => 0)
두 번째 인수 minute는 종료 시간과 시작 시간의 뒤 2개 문자를 반한하면 될 것이고요.(minute => RIGHT(셀 참조, 2))
문제는 첫 번째 인수 hour입니다. 일반적인 생각으로는 앞 2개 문자를 반환(LEFT(셀 참조,2))하면 될 것 같지만, 10시가 안 되는 시간 즉 930(9:30) 같은 3자리 문자에서는 2개가 아니라 1개를 반환(93(X)->9(O))해야 합니다. LEFT함수의 num_chars 인수가 상황에 따라 변해야 한다라는 것이죠.
그래서 참조 셀이 4자리라면 2로, 3자리라면 1로 반환할 어떤 조치가 필요한 상황입니다. 4-2, 3-2가 되면 되겠죠. 그런데 이때 4와 3은 참조 셀의 전체 문자 개수입니다. 텍스트의 전체 개수를 반환하는 함수 LEN이 다시 필요해집니다. LEN 함수를 모르신다면 아래 강좌를 참고해 주세요.
결론, 위의 경우 TIME 함수 첫 번째 인수 hour는 LEFT 함수를 사용해야 하고 추출한 문자 수는 LEFT 함수의 인수 text의 전체 문자 수에서 2를 뺀 숫자만큼의 개수를 추출해야 합니다.(hour => LEFT(셀 참조, LEN(셀 참조)-2))
TIME 함수의 인수까지 모두 알아 보았으니 실제 적용하겠습니다.
- 작성할 셀(F5)을 클릭하여 선택합니다.
- 기본 수식은 종료 시간 - 시작 시간입니다. - 수식 입력줄에서 수식을 시작하고 먼저 종료 시간을 TIME 함수로 작성합니다. => =TIME(
- TIME 함수의 첫 번째 인수(hour)를 확인하고 종료 시간 데이터 앞 1개 또는 2개 문자를 LEFT 함수로 추출합니다. => =TIME(LEFT(E5,LEN(E5)-2)
- 쉼표로 구분, TIME 함수의 두 번째 인수(minute)를 확인하고 종료 시간 데이터 뒤 2개 문자를 RIGHT 함수로 추출합니다. => =TIME(LEFT(E5,LEN(E5)-2),RIGHT(E5,2)
- 쉼표로 구분, TIME 함수의 세 번째 인수(second)를 확인하고 초는 없으므로 그냥 0으로 입력하고 괄호를 닫아 종료 시간 함수를 완성합니다. => =TIME(LEFT(E5,LEN(E5)-2),RIGHT(E5,2),0)
- 빼기를 입력하고 시작 시간을 TIME 함수로 작성합니다. => =TIME(LEFT(E5,LEN(E5)-2),RIGHT(E5,2),0) - TIME(
- TIME 함수의 첫 번째 인수(hour)를 확인하고 시작 시간 데이터 앞 1개 또는 2개 문자를 LEFT 함수로 추출합니다. => =TIME(LEFT(E5,LEN(E5)-2),RIGHT(E5,2),0) - TIME(LEFT(D5,LEN(D5)-2)
- 쉼표로 구분, TIME 함수의 두 번째 인수(minute)를 확인하고 시작 시간 데이터 뒤 2개 문자를 RIGHT 함수로 추출합니다. => =TIME(LEFT(E5,LEN(E5)-2),RIGHT(E5,2),0) - TIME(LEFT(D5,LEN(D5)-2),RIGHT(D5,2)
- 쉼표로 구분, TIME 함수의 세 번째 인수(second)를 확인하고 초는 없으므로 그냥 0으로 입력하고 괄호를 닫아 시작 시간 함수를 완성한 후 최종 엔터로 셀 수식을 반환합니다. => =TIME(LEFT(E5,LEN(E5)-2),RIGHT(E5,2),0) - TIME(LEFT(D5,LEN(D5)-2),RIGHT(D5,2),0)
결과 "1:30 AM"이 반환됩니다.
여기서 AM/PM은 의미 없습니다. 그냥 1:30분 동안 수업이 진행된다는 의미이니 있는 것이 더 혼란스럽습니다.
셀 표시 형식을 h:mm으로 변경하겠습니다.
- F5셀을 다시 클릭하여 선택한 후 ctrl+1로 셀 서식 대화 상자를 엽니다.
- 표시 형식 탭이 아니라면 표시 형식을 탭을 클릭합니다.
- 사용자 지정이 아니라면 사용자 지정을 클릭합니다. (이전에 셀 서식을 변경한 것이 아니라면 표시 형식에 사용자 지정으로 되어 있을 겁니다.)
- 형식 부분란에 h:mm을 입력하거나 클릭하여 선택합니다.
- 셀 서식 대화 상자의 확인 버튼을 클릭합니다. => 강의 시간의 AM 표시가 사라집니다.
- 최종, F5셀로 나머지 셀을 자동 채우기(우측 하단에서 클릭 드래그) 합니다.
결과, 그림처럼 올바른 시간 계산 결과를 얻을 수 있습니다.
다시 강조하지만, 처음부터 시간을 시간 표시 형식으로 작성하는 것이 가장 좋은 방법입니다. 그렇지 못했다면 빠른 채우기를 통해 시간 표시 형식으로 변경하는 것이 좋고요. 그렇게 할 수 없는 경우에만 지금처럼 TIME 함수를 사용하시길 바랍니다.
의견글 입니다.
()
