엑셀 특정날짜 표시 - egsel teugjeongnaljja pyosi

엑셀 날짜 함수 총정리 목차 바로가기

1. 입력된 날짜에서 연도/월/일/요일 따로 분리하기
2. 특정 날짜의 요일을 출력하는 방법
3. 텍스트를 날짜로 변경하기
4. 각 소모품의 교체주기 별 교체일 계산 및 경고표시 띄우기
5. 직원별 수습기간 및 최초 연봉협상일 계산하기
6. 특정요일, 공휴일 제외한 실제 근무일수 계산하기
7. 직원별 근속년수 또는 근속개월수 계산하기

영상 강의

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

강의에 사용된 엑셀 날짜 함수 목록

입력된 날짜에서 연도/월/일/요일 따로 분리하기

직원별 생년월일이 입력된 표가 있습니다. 이 표에서 직원별 나이를 계산한 뒤 각 직원의 월별 생일정보를 취합해야 할 경우 아래 3가지 함수를 사용하면 쉽게 해결할 수 있습니다.

반대로, 각 연도, 월, 일로 구분되어 입력된 값을 날짜로 변환해야 할 수도 있는데요. 이럴 때는 DATE 함수를 사용합니다.

이런 상황은 보통 가공된 데이터에서 종종 발생하는데요. 예를 들어 년도별(2017, 2018..) 또는 월별(1,2,3..) 로 열이 구분되어 있고 이렇게 입력된 값들을 하나의 날짜로 취합하여 "2017/1/1, 2017/2/1.."로 변환할 때 사용합니다.

특정 날짜의 요일을 출력하는 방법

만약 해당 날짜의 요일을 출력해야 할 경우 2가지 방법을 사용할 수 있습니다.

사용자 지정서식을 활용하기 - 보편적으로 사용

사용자 지정서식을 활용할 경우, 셀 안에 입력된 값은 날짜를 유지한 채 겉으로 표시되는 값만 요일로 변경됩니다.

따라서 요일을 출력하기 위해 사용자 지정서식 활용할 경우, 해당 셀을 참조하여 계산 가능하다는 장점이 있으므로 일반적인 상황에서는 사용자 지정서식을 활용하는 것을 권장드립니다.

TEXT 함수를 활용하는 방법

일부 특정 상황에서는 사용자 지정 서식 대신  TEXT함수를 사용할 수 있습니다. 사용자 지정 서식으로 요일이 표시된 할 경우, 셀 안에 실제 입력된 값은 날짜(=숫자)이므로 요일에 해당하는 텍스트(="월,"화","수",등...)가 필요할 경우 TEXT 함수를 사용합니다.

요일을 숫자로 변환하는 방법

각 날짜별 요일에 따라 숫자로 변환하여 계산이 필요할 경우도 종종 발생합니다. 예를 들어 월요일은 +1, 화요일은 +2 .. 를 더하는 방식으로 계산 해야 할 경우, WEEKDAY 함수를 사용하면 편리합니다.

WEEKDAY함수를 사용한 또 다른 예제로 자동화 달력 만들기 강의를 준비해드렸습니다. 보다 자세한 내용은 아래 관련 강의를 참고하세요.

텍스트를 날짜로 변경하기

여러 부서에서 자료를 취합하다 보면, 분명히 날짜로 보이는 값인데 날짜 계산이 안되거나 함수 결과값으로 #VALUE! 오류가 출력되는 경우가 종종 발생합니다.

그 원인은 바로 텍스트 형식으로 입력된 날짜 때문인데요. 텍스트형식의 날짜를 일반 날짜형식으로 한번에 쉽게 변환하는 방법을 알아보겠습니다.

1. 날짜형식으로 변환하고자 하는 범위를 선택합니다. [데이터] – [텍스트 나누기] – [구분 기호로 분리됨] 을 선택한 뒤 [다음]을 눌러 그 다음 단계로 넘어갑니다.
   
   
   [데이터] – [텍스트 나누기] 기능을 이용하여 텍스트로 입력된 날짜를 손쉽게 변경할 수 있습니다.
2. 2단계에서는 바로 [다음]을 눌러 3단계로 이동합니다. 3단계 대화상자의 ‘열 데이터 서식’ 에서 ‘날짜’를 [년월일]로 선택한 후 [마침]을 눌러 날짜형식 변환을 마무리합니다.
   
   
   [날짜]에서 [년월일]을 선택한 후 [마침]을 눌러 날짜변환을 완료합니다.
3. [텍스트 나누기] 기능을 활용해도 날짜형식으로 변환되지 않는 텍스트 형식의 날짜가 있을 수 있습니다. (특히 년/월/일로 나뉘어져 입력된 날짜의 경우가 그렇습니다.) 그럴 경우 아래 수식을 복사한 뒤 우선 아무 셀에 수식을 붙여넣기 합니다. 수식을 붙여넣기 하면 #NAME? 오류가 나옵니다. 바로 다음단계로 넘어갑니다.

   =DATE(LEFT(셀,FIND("년",셀,1)-1),MID(셀,FIND("년",셀,1)+1,FIND("월",셀,1)-FIND("년",셀,1)-1),MID(셀,FIND("월",셀,1)+1,LEN(셀)-FIND("월",셀,1)-1))

4. 수식을 붙여넣기 한 셀을 선택한 뒤, 키보드 CTRL + H 키를 눌러 [찾기 및 바꾸기] 대화상자를 실행합니다.

   찾을 내용 : 셀
   바꿀 내용 : B8 (텍스트로 입력된 날짜의 셀 주소)
   찾는 위치 : 수식 ⇨  [모두 바꾸기] 버튼 클릭

   
   [찾기 및 바꾸기] 기능을 활용하여 수식의 ‘셀’ 로 입력된 텍스트를 해당 셀주소로 한번에 변경합니다.
5. 안내메세지가 뜨면서 해당 수식의 결과값으로 올바른 형식의 날짜가 반환됩니다.
   
   
   [안내메세지]가 뜨면서 올바른 형태의 날짜가 수식의 결과값으로 반환됩니다.

각 소모품의 교체주기 별 교체일 계산 및 경고표시 띄우기

총무부에서 일하게 되면 소모품 또는 라이선스 등의 갱신일을 수시로 점검해야 합니다.

특히 여러 개의 매장을 관리중인 경우 또는 개개인의 별도 프로그램 라이선스를 발급받아 사용 중일 경우에는 각 항목별 납기일을 수시로 챙겨줘야 하므로 실무자의 지속적인 관리가 필요합니다.

만약 엑셀로 해당 자료를 관리할 경우, EDATE 함수를 사용하면 각 항목의 교체주기에 따라 다음 교체일을 자동 계산할 수 있습니다.

또한 TODAY 함수와 같이 응용하면 각 제품별 교체일이 지났을 경우, 또는 교체일이 다가올 시 해당 셀에 경고표시를 띄워 담당자의 실수를 사전에 방지할 수 있습니다.

엑셀 날짜 함수 직원별 수습기간 및 최초 연봉협상일 계산

지정한 날로부터 특정 개월이 지난 달의 마지막 날을 계산해야 할 상황도 종종 발생합니다. 예를 들어, 급여 또는 대금 결산의 대부분은 매월 말일을 기준으로 계산되므로, 이러한 월말 기준의 날짜계산은 인사 및 총무 업무에서 자주 필요로하는 작업 중 하나입니다.

이번 강의에서는 각 직원의 입사일 기준으로 3개월이 지난 뒤의 수습기간 종료일을 계산하는 예제를 다뤘는데요. 각 수습기간은 3개월이 지난 달의 마지막 날 종료된다고 가정하였습니다.

보통 수습기간에는 연봉협상이 이루어지지 않으므로, 수습기간이 끝난 뒤의 최초 연봉 협상일을 계산하는 방법도 알아보았습니다. 예제에서는 매년 4월 연봉협상을 한다고 가정했는데요.

따라서, 수습기간 종료일이 4월 이전일 경우 같은 해 4월 1일날 연봉협상을 하며, 그렇지 않을 경우 다음해의 4월 1일날 연봉협상을 하도록 수식을 입력합니다.

특정요일, 공휴일 제외한 실제 근무일수 계산하기

대부분의 일용직, 아르바이트 직원의 급여는 실제 근무일수를 기준으로 계산합니다. 따라서 정규직처럼 정해진 주말과 공휴일에만 쉬는 것이 아닌, 특정 요일(월/수/금)에 쉬는 경우에는 별도의 계산식을 넣어줘야 하는데요.

엑셀 날짜 함수 중 하나인 NETWORKDAYS.INTL 함수를 사용하면 특정 요일 이외에도 불규칙적으로 발생하는 공휴일까지 고려하여 실제 근무일수를 쉽게 해결할 수 있습니다. (엑셀 2010 이후 버전에서 사용 가능합니다.)

이번 강의에서는 공휴일 범위에 새롭게 추가되는 공휴일을 자동으로 계산 범위에 반영하기 위해 동적범위를 사용하였습니다. 동적범위에 대한 자세한 설명은 아래 관련 강의를 참고해주세요.

예제파일에서 키보드 CTRL + F3키를 눌러 이름관리자를 실행한 뒤, 아래 수식으로 새로운 사용자지정범위를 생성합니다.

=OFFSET('근무일계산(주말or휴일선택제외)'!$M$8,,,COUNTA('근무일계산(주말or휴일선택제외)'!$M$8:$M$1000))

엑셀 날짜 함수 직원별 근속 년수 계산하기

직원별 근속년수와 근속개월수를 계산합니다. YEAR 함수와 MONTH 함수를 응용하여 계산할 수도 있지만, DATEDIF 함수를 사용하면 하나의 함수로 더욱 간단히 계산할 수 있습니다.