예제 파일을 보겠습니다.
예제 파일은 한 중고폰 거래 매장의 중고폰 매입 현황입니다. 사용자들에게 중고폰을 구매하고 점검하여 다시 판매하는 매장입니다. 사용일 항목에는 사용자들로부터 매입한 중고폰이 얼마 동안 사용되었는지를 파악하려 합니다. 매입일에서 구매일을 빼면 간단하게 사용 기간을 알아낼 수 있습니다.
그런데 문제가 있습니다. 사용자 구매일과 매입일이 모두 날짜의 표시 형식으로 작성되지 않고 그냥 일반의 표시 형식으로 작성된 것입니다.
수식을 한번 적용해 보겠습니다.
F5셀을 클릭하고 수식 입력줄에서 매입일에서 사용자 구매일을 뺀 수식을 작성합니다.
결과 119일이 반환됩니다. 맞나요? 9월 25일은 8월 6일에서 2달이 지나지 않은 날인데 119일이라니 말이 되지 않죠.
별생각 없이 이런 식으로 계산하면 잘못된 결과를 얻게 됩니다. 모두 아시겠지만, 8월은 31일까지만 있습니다. 32, 33, ... 없죠. 위 수식은 그냥 숫자 925 - 806을 당연한 결과입니다. 그렇다고 없는 날을 위 수식에서 빼주는 작업을 정말 골치 아픈 일이죠.
그럼, 사용자 구매일과 매입일을 날짜의 표시 형식으로 다시 작성하고 결과를 확인해 보겠습니다.
그림처럼 사용자 구매일과 매입일을 다시 날짜 표시형식으로 작성하고 동일하게 수식을 적용하면 50일이라는 올바른 결과를 얻을 수 있습니다.
왜 이렇게 되는지는 기초 강좌 날짜와 시간의 이해 편에서 설명했습니다. 잘 모르는 얘기라면 아래 강좌를 참고해 주세요.
문제가 뭐라는 건지 아셨을 걸로 생각합니다. 날짜의 월은 우리가 사용하는 숫자 10진수가 아니고 30, 31, 28, 29진수가 복잡하게 사용된다는 것입니다.
그래서 처음부터 사용자 구매일과 매입일이 날짜 표시형식대로 작성되었다면 아무런 문제 없이 단순 빼기로 처리할 수 있던 것을 지금은 할 수 없게 된 것이죠.
해결 방법은 2가지입니다.
첫 번째 방법은 위처럼 사용자 구매일과 매입일을 모두 날짜 표시 형식으로 변경하는 것입니다. 그리고 그냥 빼기하면 되죠. 이렇게 말씀드리면 "저 많은 데이터를 어느 세월에 다시 작성해"라고 생각하시는 분들이 있을 텐데, 사실 빠른 채우기를 이용하면 손쉽게 바꿀 수 있습니다. 만약 다시 사용자 구매일과 매입일로 어떤 계산해야 할 경우가 있다면 이 방법이 가장 확실한 해결책입니다. 이 방법은 빠른 채우기 강좌 편을 참고하시고 직접 해 보시길 바랍니다.
두 번째 방법은 DATE 함수를 사용하는 방법입니다. 첫 번째 방법이 근본적인 해결책이지만, 어떤 이유로 원본의 표시 형식을 변경해선 안 된다면 이 함수를 사용하여 지금처럼 작성된 숫자를 날짜 표시 형식으로 반환하여 사용할 수 있습니다.
DATE인수 year, month, day를 날짜 표시 형식으로 반환
DATE(year,month,day)| year | 년 (yyyy 표시 형식 추천)
기준은 1900년이므로 1900 미만의 수는 1900이 더한 년으로 계산 (ex, 108 => 1900+108=2008) |
| month | 월 (mm 표시 형식 추천)
- 1월에서 12월 사이의 월을 나타내는 양의 정수나 음의 정수
- 12보다 크면 그 값을 지정된 연도의 첫 번째 달에 더해 계산 (ex, DATE(2008,14,2) => 2009-02-02)
- 음수이면 year의 이전 연도(year-1) 마지막 월(12월)에서 빼는 방식으로 계산 (ex, DATE(2008,-3,2) => 2007-02-02)
|
| day | 일 (dd 표시 형식 추천)
- 1일에서 31일 사이의 일을 나타내는 양의 정수나 음의 정수
- 지정된 달의 일 수보다 크면 그 값을 지정된 달의 첫째 날짜에 더하여 계산 (ex, DATE(2008,1,35) => 2008-02-04)
- 음수이면 month의 이전 월(month-1) 마지막 날짜에서 빼는 방식으로 계산 (ex, DATE(2008,1,-15) => 2007-12-16) |
함수 정리는 위와 같습니다. 2가지 주의하셔야 할 사항이 있습니다.
첫 번째 엑셀에서 기준이 되는 연도는 1900년이라는 것입니다. 그래서 year 인수를 1900 이상으로 사용하면 아무 문제가 없지만 1900보다 작은 수를 사용하면 1900을 더한다는 점을 기억해 주세요.
두 번째 month와 day 인수에는 음수 값이 들어올 수 있으며, 0월과 0일이 없기 때문에 이전 연도의 마지막 월과 다음 연도의 첫 번째 월, 이전 달의 마지막 일과 다음 달의 첫 번째 일을 고려하여 계산된다는 점입니다. 어렵게 생각하실 필요 없습니다. 10진수 계산이 날짜로 반영된다는 뜻입니다.
위 예제의 경우는 날짜를 계산할 필요는 없습니다. 단순히 숫자로 작성된 날짜에서 년, 월, 일을 추출하여 DATA 함수에 적용하면 됩니다.
그런데 년, 월, 일을 글자 수로 분리하고 추출하려면, LEFT, MID, RIGHT 함수를 사용해야 한다는 것이죠. LEFT, MID, RIGHT 함수를 모르신다면 아래 강좌를 참고해 주세요.
위 예제 사용일에 매입일을 날짜 표시 형식으로 반환해 보겠습니다.
- 작성할 셀(F5)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =DATE(
- 첫 번째 인수 힌트(year)를 확인하고 매입일 데이터 앞 4개 문자를 추출합니다.
- 앞 문자를 추출하기 위해 LEFT 함수명을 작성 => =DATE(LEFT(
- LEFT 함수의 첫 번째 인수 힌트(text)를 확인하고 E5셀을 참조. => =DATE(LEFT(E5
- 쉼표로 구분, LEFT 함수의 두 번째 인수 힌트(num_chars)를 확인하고 가져올 문자 개수(4)를 입력한 후 닫힌 괄호까지 입력합니다. => =DATE(LEFT(E5,4)
- year 인수로 LEFT 함수를 사용했습니다. - 쉼표로 구분, 두 번째 인수 힌트(month)를 확인하고 매입일 데이터 5번째 문자부터 2개를 추출합니다. => =DATE(LEFT(E5,4),
- 중간 문자를 추출하기 위해 MID 함수명을 작성 => =DATE(LEFT(E5,4),MID(
- MID 함수의 첫 번째 인수 힌트(text)를 확인하고 E5셀을 참조. => =DATE(LEFT(E5,4),MID(E5
- 쉼표로 구분, MID 함수의 두 번째 인수 힌트(start_num)를 확인하고 가져올 문자의 시작 위치(5)를 입력합니다. => =DATE(LEFT(E5,4),MID(E5,5
- 쉼표로 구분, MID 함수의 세 번째 인수 힌트(num_chars)를 확인하고 가져올 문자 개수(2)를 입력한 후 닫힌 괄호까지 입력합니다. => =DATE(LEFT(E5,4),MID(E5,5,2)
- month 인수로 MID 함수를 사용했습니다. - 쉼표로 구분, 세 번째 인수 힌트(day)를 확인하고 매일일 데이터 뒤 2개의 문자를 추출합니다. => =DATE(LEFT(E5,4),MID(E5,5,2),
- 뒤 문자를 추출하기 위해 RIGHT 함수명을 작성 => =DATE(LEFT(E5,4),MID(E5,5,2),RIGHT(
- RIGHT 함수의 첫 번째 인수 힌트(text)를 확인하고 E5셀을 참조. => =DATE(LEFT(E5,4),MID(E5,5,2),RIGHT(E5
- 쉼표로 구분, RIGHT 함수의 두 번째 인수 힌트(num_chars)를 확인하고 가져올 문자 개수(2)를 입력한 후 닫힌 괄호까지 입력합니다. => =DATE(LEFT(E5,4),MID(E5,5,2),RIGHT(E5,2)
- day 인수로 RIGHT 함수를 사용했습니다. - 최종 DATE 함수의 괄호를 닫고 엔터로 셀을 완성합니다. => =DATE(LEFT(E5,4),MID(E5,5,2),RIGHT(E5,2))
결과, 2025-09-25로 반환됩니다. 복잡해 보이지만 원리를 생각하면 어렵지 않습니다. year, month, day 인수를 모두 LEFT, MID, RIGHT 함수를 통해 추출한 것입니다.
같은 방법으로 사용자 구매일도 날짜 표시 형식으로 반환할 수 있습니다. 그리고 빼주면 되겠죠.
사용자 구매일은 직접 해 보시길 바랍니다. 위 수식과 동일하고 모든 참조를 E5셀에서 D5셀로 변경하면 됩니다. 수식만 확인하겠습니다.
결과,
1900-02-19로 반환됩니다. 숫자로 표시되지 않고 날짜로 표시되는 이유는 F5셀에서 매입일을 이미 날짜 표시 형식으로 한번 반환했었기 때문입니다. F5셀의 표시 형식이 이전 작업에서 날짜 표시 형식으로 바뀐 것이죠. 셀 값 50은 날짜 표시 형식으로, 1900년 1월 1일부터 50일째 되는 날인 1900년 2월 19일입니다. 즉, 표시 형식만 변경하면 원래 계산값으로 표현되다는 얘기입니다.
- F5셀을 클릭하여 선택합니다.
- 홈 > 쉼표 버튼을 클릭합니다. => 1900-02-19가 50으로 바뀌고 표시 형식이 천 단위 구분기호가 들어간 회계 형식으로 바뀝니다.
- 최종, 선택된 F5셀로 자동 채우기(우측 하단에서 더블 클릭하거나 클릭 드래그)를 진행하여 나머지 셀을 완성합니다.
결과, 그림처럼 올바른 사용일이 구해집니다.
한 번 더 말씀드리지만, 근본적인 해결책은 잘못된 표시 형식으로 만들었던 날짜를 날짜 표시 형식으로 만드는 것입니다. DATE 함수를 사용해야 할 경우는 그렇게 하면 안 되는 경우만 사용하는 것이고요. 가능한 빠른 채우기를 통해 날짜 표시 형식으로 변경하고 단순하게 계산할 방법으로 사용하시길 추천합니다.
그리고 역시 가장 좋은 방법은 문서를 작성할 때 처음부터 날짜의 표시 형식대로 작성하는 것입니다. 그랬다면 이 강좌를 보실 필요도 없었겠죠.
의견글 입니다.
()
