티스토리 뷰

지난 포스팅에서 엑셀은 표시형식 기능 떄문에 '계산된 값'과 '표시되는 값'이 서로 다를 수 있다는 것을 설명드린 바 있습니다. 이러한 일은 특히 엑셀에서 날짜나 시간 같은 값을 다룰 때 가장 흔히 발생합니다. 왜냐하면 날짜나 시간은 표시형식에 따라 정말 다양한 형태로 표현되기 때문입니다.

 

예를 들어 보겠습니다.

같은 값, 다른 표시

위 그림에 나와있는 [A2:A10] 셀에 제가 입력한 값은 모두 '2020-10-06' 으로 동일합니다. 그러나 표시되는 방식은 완전 제각각인 것을 알 수 있습니다. 이는 제가 '표시형식'을 각각 다르게 설정해놓았기 때문인데요. 어떻게 하면 이렇게 만들 수 있는 지 알아보겠습니다.

 

1. 엑셀 날짜 값의 이해

 

엑셀은 날짜를 자연수로 인식합니다. 아니, '엑셀은 자연수를 날짜로 변환합니다.'라고 말하는 게 더 정확할 것 같습니다.

엑셀이 숫자를 날짜로 변환하는 규칙은 다음과 같습니다.

  1. 숫자 1을 '1900-01-01'로 친다.
  2. 숫자 2를 '1900-01-02'로 친다.
  3. 이렇게 하루가 지날 때마다 숫자 1을 더해 날짜를 매칭시킨다.
  4. 가장 마지막 날자는 '9999-12-31'이고 이 때의 숫자는 2,958,465 이다.

 

그럼 실제 예를 살펴 보실까요?

엑셀의 날짜 지정방식

 

  • 1900-01-01 은 숫자 1과 동일합니다.
  • 1900-02-29 의 값이 60으로 나오네요. 즉, 1900년은 윤달이 있는 해였던 것입니다.
  • 1900-12-31 의 값은 365가 아니라 366입니다. 2월에 윤달이 있었기 때문입니다.
  • 2000-01-01 의 값은 36,526입니다. 백년 간 윤달이 낀 해가 25번 있었나 봅니다.
  • 2020-10-06 의 값은 44,110입니다. 엑셀에 44111을 입력하고 날짜로 표시하면 '2020-10-07'이 뜨겠구나 예상할 수 있습니다.
  • 9999-12-31 이 엑셀에서 숫자로 표시 가능한 마지막 날짜입니다. 값은 2,958,465입니다.

 

엑셀은 1899-12-31 이하의 날짜나 10000-01-01 이후의 날짜에 매칭되는 숫자를 지정해놓지는 않았습니다. 

 

따라서 1900-01-01 보다 1 작은 수나, 9999-12-31 보다 1 큰 수를 입력해 비교해 보면 두 값은 다른 값이라는 결과가 나오게 됩니다.

 

2. 엑셀 시간 값의 이해

 

엑셀은 시간을 0부터 86399/86400까지의 소수로 인식합니다. 이 역시 보다 정확히 '엑셀은 각각의 분수를 시간으로 변환한다'고 말하는 게 정확할 것 같습니다. 구체적으로는 이렇습니다.

 

  1. 숫자 0을 0시(AM 12:00:00)으로 둡니다.
  2. 이후 1/86400 이 커질 때마다 1초씩 더하는 것으로 간주합니다. 왜냐하면 하루를 초로 계산하면 24시간*60분*60초가 되어 86400초가 나오기 때문입니다.
  3. 즉, 엑셀은 숫자 2/86400 을 0시00분02초로 인식하고, 숫자 86399/86400 을 23시59분59초로 인식합니다.

 

 

위에서는 편하게 분수로 설명하였지만 엑셀은 사실 소수로 숫자를 관리합니다. 그래도 우리가 인식하기에는 분수가 편하니 분수로 설명드리겠습니다.

 

  1. 1/86400을 엑셀은 0시00분00초로 인식합니다. 소수로는 0.0001157.... 정도인데 우리가 신경 쓸 부분은 아닙니다. 다만, 0:00:01이나, 1/86400 이나 엑셀은 같은 값으로 인식하고 있다는 것만 확인해주시면 됩니다.
  2. 1/24를 엑셀은 1:00:00 으로 인식합니다. 즉, "하루의 24 분의 1이 지난 시점이니 오전 1시로 치면 되겠구나"라고 엑셀이 인식한다는 것만 알아두시면 됩니다.
  3. 1/2 는 마찬가지로 오후 12시, 즉 하루의 반이 지난 시점을 의미합니다.

 

3. 날짜와 시간을 함께 사용

 

엑셀은 날짜에는 1부터 시작하는 자연수를, 시간에는 0부터 0.9999884... 에 이르는 소수를 매칭하기 때문에 날짜와 시간데이터를 숫자 하나에 동시에 기록하여 사용할 수 있습니다. 예를 들면 다음과 같습니다.

  1. 숫자 [12.25]는 자연수 [12]와 소수 [0.25]로 구성된다 볼 수 있습니다. 각각 [1900년 1월 12일]과 [AM6:00:00]을 의미하기에 [12.25]는 엑셀이 [1900년 1월 12일 AM6:00:00] 으로 인식한다는 의미입니다.
  2. 마찬가지로, 현재날짜인 [2020년 10월 06일 AM3:12:00]을 엑셀에 입력해서 숫자로 변환해보면 44110.13333... 이 나옵니다. 

 

 

4. 엑셀 표시형식

 

엑셀의 매력은 표시형식을 통해 숫자로 저장된 [날짜], [시간]값에서 원하는 표시 방식을 선택할 수 있다는 점입니다.

같은 44100.13333... 이란 숫자를 가지고 [2020년 10월 06일 AM3:12:00] 으로 표시할 수도, [20/10/6 AM3]이라고 표시할 수도, 아니면 아예 년도와 시간을 빼버리고 요일을 넣어 [10월6일 화요일]이라고 표시할 수도 있다는 말입니다.

바로 이렇게 말입니다.

 

이렇게 설정하는 방법은 간단합니다. [셀 서식(Ctrl+1)]에 들어가 [표시 형식]을 우리가 원하는 형식으로 설정해주면 됩니다. 바로 이렇게 말이죠.

엑셀 표시형식

 

원하는 [형식]을 선택하면 [보기]에 예시를 보여주기 때문에 원하시는 형태를 찾아 골라주시면 됩니다. 다만, 이 방법을 사용하게 되면 엑셀에서 제공해주는 표시형식 밖에 사용하지 못하기 때문에 자유로움이 떨어진다는 단점이 있습니다. 때문에 저는 주로 [사용자 지정] 방식을 사용하는 것을 선호하는 편입니다.

 

 

5. 엑셀 [사용자 지정] 표시형식을 사용하기 위해 알아야 할 것들

 

엑셀 [사용자 지정] 표시방식은 다음과 같이 [형식]을 직접 입력해주는 방식으로 작동합니다. 이를 위해 [형식]을 지정하기 위한 문자열을 알아두어야 할 필요가 있습니다.

 

 

이처럼 [형식]에 쓰이는 문자열 중 [날짜], [시간], [요일]에 관련된 문자열들만 모아보면 다음과 같은 종류가 있습니다.

 

 

  1. [년]에 관란 문자열 : yyyy를 입력하면 4자리 연도(예:2020)로, yy를 입력하면 2자리 연도(예:20)가 표시됩니다.
  2. [월]에 관한 문자열 : mmmmm, mmmm, mmm은 영어로 된 달(예:Octorber)을 몇 글자까지 표시하느냐를 결정합니다. mm과 m의 차이는 한 자리 숫자 앞에 0을 넣을 것이냐(예:09월) 안 넣을 것이냐(예:9월)를 결정합니다.
  3. [일]에 관한 문자열 : dd와 d 도 마찬가지로 한 자리 숫자 앞에 0을 넣을 것(예:09일)인지 안 넣을 것인지(9일)을 결정합니다. 이하 다른 문자열에서도 마찬가지입니다.
  4. [요일]에 관한 문자열 : 한글로 표현할 때는 aaaa(전체)와 aaa(첫 글자)를, 영어로 표현할 때는 dddd(전체)와 ddd(세 글자)를 사용합니다.
  5. [시각]에 관한 문자열 : hh와 h는 앞서 말씀드린 대로 한 자리 숫자 앞에 0을 넣을 것인지를 결정합니다. [h]와 같이 대괄호로 묶으면 소숫점 위의 숫자도 모두 시간으로 환산하여 계산해줍니다. 24시간이 넘어가는 시간을 25시간, 48시간 등으로 표현할 때 유용합니다. 이하, [분]과 [초]에서도 마찬가지입니다.
  6. [분]에 관한 문자열 : [월]에 관한 문자열과 동일하게 mm과 m을 사용합니다. 따라서 시간(h류)이나 초(s) 문자열과 함께 사용해야만 분으로 인식하고 작동합니다. [m]은 분에만 쓰이는 개념이기에 시간이나 초가 없어도 월이 아닌 분으로 인식합니다.
  7. [초]에 관한 문자열 : [시각], [분]의 작동 방식과 동일합니다.
  8. [오전/오후]에 관한 문자열 : AM/PM 과 am/pm 은 대문자 소문자를 결정합니다. A/P와 a/p를 사용하면 1자리 글자만 사용합니다. 이때, 시간 문자열(hh 혹은 h) 주변에 AM/PM이 없으면 엑셀은 시간을 24시간제로 표현합니다. 영어 대신 한그롤 표현하려면 [$-ko-KR]을 붙여줘야합니다.

 

6. 자유로운 조합으로 날짜와 시간을 표현해보자

 

이러한 [사용자 지정] 방식의 [표시형식]의 장점은 키워드들을 조합하여 자유자재로 내가 원하는 표현을 할 수 있다는 것입니다. 앞서 든 예시입니다. 각각 어떤 식으로 [표시형식]을 입력하였는지 감이 잡히시나요?

 

  1. [A2]셀 : yyyy년 mm월 dd일 AM/PMh:mm:ss 
  2. [A3]셀 : yy/m/d AM/PMh
  3. [A4]셀 : m월d일 aaaa

 

이처럼 엑셀이 [날짜]와 [시간]을 어떤 식으로 관리하고 표현하는 지를 이해하게 된다면 앞으로 정말 많은 것들을 할 수 있습니다. 차차 소개해드리도록 하겠습니다.

댓글