엑셀 시간 값 비교하는 방법

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예시

 

 

01. 문제

Q. 입력시간이 저촉시간 전~사이에 들어가는거만 개수를 세고자하는데 어떠한 함수를 이용해야 하나요..?

 

예시) 입력시간     저촉시간

          오후 1:43    13:01~14:01    참

 

으로 나타내고자 합니다.

 

데이터의 예시는 다음과 같습니다.

 

문제를 정리하면 다음과 같다.

(1) 입력시간은 엑셀의 시간 형태로 입력된다.

(2) 저촉시간은 두 시간이 결합된 텍스트 형태로 제공된다.

(3) 입력시간이 저촉시간에 포함되면 1을 반환하고, 포함되지 않은 경우 0을 반환한다.

(4) 분류한 1과 0에 대해 합 연산을 한 값을 결과로 취한다.

 

02. 문제 해결 방법

(1) 엑셀에서의 시간

엑셀의 셀에  시간 형태의 텍스트 값을 입력하면 엑셀에서 저절로 시간으로 값을 바꾸게 된다.

 

다음과 같이 시간 형태의 텍스트를 입력할 경우

엑셀 프로그램은 이 입력을 시간으로 인식하여 값을 시간으로 저장하게 된다. 

이 값은 시간으로 저장되어 있는 것을 확인할 수 있으며, 셀 서식의 표시형식을 일반으로 선택할 경우 해당 값이 소수로 변하는 것을 확인할 수 있다.

엑셀에서 시간은 소수로 저장된다고 볼 수 있다. 참고로 날짜는 정수로 표현된다. 날짜와 시간을 같은 값이 모두 포함하고 있다면 정수부와 소수부를 구분하여 날짜/시간을 각각 추출하는 것이 가능하다.

 

(2) 텍스트에서 원하는 정보 추출하여 시간으로 변경하기

일단 알아야되는 함수들에 대한 포스트들을 다음과 같이 나열한다.

* LEFT 함수/FIND 함수 (특정 문자의 위치 알아내기, 앞에서부터 잘라내기)

https://eggdrop.tistory.com/56

엑셀 문자열 처리 (LEFT함수, RIGHT함수, LEN함수, FIND함수)

* MID 함수 (중간에서 문자열 잘라내기)

https://eggdrop.tistory.com/57

엑셀 문자열 처리 (MID함수, REPLACE함수, SUBSTITUTE 함수, 문자열 포함 여부 확인)

* TIMEVALUE 함수 (문자열을 시간으로 변경)

https://blog.naver.com/excali/222942162485

엑셀 시간 관련 함수 (TIME, TIMEVALUE)

 

(2-1) 저촉시간의 시작 시간 추출

저촉시간은 다음과 같은 형태로 사용된다.

13:01~14:00(오후)

 

여기서 13:01만 추출하기 위해서는 Left 함수와 find함수를 사용해야 한다.

 

일단 시작 시간 뒤에 항상 등장하는 ~(물결문자)의 위치를 알아내보자.

=FIND("~",B2,1)

 

물결표시가 등장하는 위치를 나타내는 수식으로 결과는 다음과 같다.

 

물결표시는 13:01~14:00(오후)의 6번째 문자이다.

 

이제 LEFT 함수를 이용하여 ~문자가 나오기 전까지의 문자를 잘라내보자.

=LEFT(B2,C2-1)

 

B2에서 입력된 값을 ~문자가 발생하는 위치 하나 전의 길이까지만 잘라내면 저촉시간의 시작 값을 구할 수 있다.

결과는 다음과 같다.

13:01만 추출되는 것을 확인할 수 있다. 이 값은 텍스트라는 것에 주의하자.

 

(2-2) 저촉시간의 종료 시간 추출

이번에는 저촉시간의 종료시간을 추출해보자.

종료시간은 mid함수와 find 함수를 이용하여 추출할 수 있다.

종료 시간이 시작되는 위치는 ~문자의 다음이다.

종료 시간이 끝나느 위치는 왼쪽괄호( 가 등장하는 위치의 바로 직전이다.

 

일단 왼쪽괄호의 위치를 다음과 같이 구해보자.

=FIND("(",B2)

구한 위치 값은 다음과 같다.

 

이제 MID함수를 이용하여 14:00만 추출해보자.

=MID(B2,C2+1,E2-C2-1)

- MID함수의 두번째 인자는 잘라낼 시작 위치를 입력하면 되는데, 여기서는 ~문자 다음부터 잘라내야하므로 C2셀의 값에 1을 더한 값을 입력하면된다. 

- MID함수의 세번째 인자는 잘라낼 문자열의 길이를 나타낸다. 여기서는 왼쪽 괄호의 위치에서 물결 문자의 위치를 뺀 값에서 1을 추가로 뺀 값을 사용하면된다. 이렇게 되면 잘라낼 문자열의 길이가 나온다.

 

결과는 다음과 같다.

여기서 추출한 저촉시간(끝) 값또한 텍스트이다.

 

(3) 시간 값 비교하기

위에서 저촉시간의 시작값과 종료값을 텍스트 형태로 구했다.

이제 이 값들을 시간으로 변환시켜보자.

 

텍스트를 시간으로 변환하기 위해서는 TimeValue 함수를 사욯하면된다.

저촉시간 시작과 저촉시간 끝을 시간으로 변환한 값은 다음과 같다.

 

이제 입력시간이 G2, H2에서 구한 시간 사이에 있으면 입력시간이 저촉시간에 들어간다고 볼 수 있다. 만약 입력시간이 G2, H2에서 구한 시간 사이에 존재하지 않으면 입력시간이 저촉시간에 들어간다고 볼 수 없다.

 

다음과 같으 수식으로 입력시간이 저촉 시간이 들어가는지를 판단 할 수 있다.

 

=IF(AND(G2<=A2,H2>=A2),1,0)

 

03. 예시

이제 위에서 구한 수식을 전체 데이터에 대해 적용해보자.

위와 같은 결과를 얻을 수 있다. 입력시간이 저촉시간 안에 들어가는 경우만 저촉시간 만족 여부 행에 1로 표기되는 것을 확인할 수 있다.