안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
엑셀(excel)작업을 하면서 자신의 경력 기간 또는 부서간 근무 기간을 계산하고 경력기간의 합계 또는 각 부서간의 근무 기간을 합산하는 경우가 생길 수 있습니다.
앞서 경력기간은 시작일자(날짜)와 종료일자(날짜)를 기준으로 근속년수, 개월수, 일수, 00년00월00일 기간으로 계산하여 표기 하는 수식 작성 방법에 대해 기록하고 작성해 보았습니다.
엑셀(Excel) 시작일자(날짜)로부터 종료일자(날짜)까지 경과일 계산 하는 수식을 작성해 보자.
시작일자와 종료일자 사이의 시간은 년, 개월, 일수로 계산하여 표기 하는 결과값이 숫자로만 표기될 경우에는 단순 sum함수 합계로 날짜 기간의 합계를 계산할 수 있지만 00년00월00일과 같이 텍스트를 포함하는 기간으로 입력된 경우에는 sum함수로 합계를 계산 할 수 없게됩니다.
따라서 이번 내용에서는 텍스트를 포함한 날짜 기간을 합산 또는 합계를 계산하는 수식 작성 방법으로 경력기간의 합계를 계산하는 예제로 함께 확인해 보도록 하겠습니다.
1. 텍스트가 포함된 경력기간 합계 또는 날짜기간 합계 계산 샘플 예제 서식.
텍스트가 포함된 경력기간 또는 날짜 기간의 합계를 계산하기 위한 수식을 작성해 보기 위해서 A열에는 순번, B열은 근무 부서, C열은 입사일, D열은 퇴사일, E열은 경력기간을 작성하는 샘플 예제 서식을 간단하게 작성해 보았는데 A열부터 D열까지는 직접 데이터 값을 입력해야 하고, E열에는 수식을 작성하여 입사일과 퇴사일 사이의 경력 기간을 "00년 00월 00일"과 같이 경력 기간을 계산 하는 수식을 작성하여 결과값을 표기 합니다.
E2셀부터 E7셀까지 각각의 경력기간을 계산하는 수식을 작성하여 결과값을 표기 하였다면 E8셀에는 총 경력 기간의 합계를 계산 하고, E9셀에는 외식사업부에서 근무한 경력기간만을 합산, E10셀에는 경영지원 부서에서 근무한 경력기간을 합산, E11셀에는 생산관리 경력 기간의 합계를 계산 하는 수식을 작성하여 결과값을 표기 하도록 합니다.
염두해 두어야 하는것은 E열에는 "00년 00월 00일"과 같은 형태로 텍스트를 포함하는 기간으로 결과값이 표기 되어 있기 때문에 숫자만을 합산하는 단순 sum함수로는 원하는 결과값을 표기하지 못한다는것입니다.
2. 텍스트가 포함된 경력기간 합계 수식 작성.
샘플 예제 서식의 각각의 텍스트가 포함된 재직기간을 합산하는 수식과 텍스트가 포함된 재직기간에 조건이 있는 경우의 합계를 작성하는 수식을 작성해 보도록 합니다.
먼저 경력기간 또는 재직기간은 00년00월00일과 같이 E2셀부터 E7셀에 텍스트가 포함된 경력기간 또는 재직간으로 결과값을 표기 하는 수식은 이전에 작성한 글을 함께 참고해 보시면 좋겠습니다.
엑셀(Excel) 시작일자(날짜)로부터 종료일자(날짜)까지 경과일 계산 하는 수식을 작성해 보자.
2-1. "00년00월00일"과 같이 텍스트가 입력된 형태로 날짜와 날짜 사이의 기간으로 경력기간 또는 재직기간 계산 수식 작성.
E2셀 기준으로 각각의 경력기간 또는 재직기간을 작성 하는 수식은 다음과 같이 작성하고 E7셀까지 수식 복사 하여 사용할 수 있습니다.
| =IF(D2="현재 재직중", DATEDIF(C2,TODAY(),"y")&"년"&DATEDIF(C2,TODAY(),"ym")&"월"&DATEDIF(C2,TODAY(),"md")&"일", DATEDIF(C2,D2,"y")&"년"&DATEDIF(C2,D2,"ym")&"월"&DATEDIF(C2,D2,"md")&"일") |
위와 같이 퇴사일이 "현재 재직중"이라는 텍스트가 입력 되어 있다면 퇴사일을 경력기간 또는 재직기간을 계산하는 일자를 오늘날짜로 하기 위해서 퇴사일의 셀 주소에 "현재 재직중"이라는 텍스트가 입력 되어 있다면 퇴사일은 today()로 현재 날짜를 퇴사일로 계산하고 그렇지 않으면 시작일자는 입사일, 종료일자는 퇴사일로 하여 경력기간 또는 재직기간을 계산 할 수 있습니다.
샘플 예제 서식에서는 현재 재직 기간중인 경우에도 경력기간 또는 재직기간을 수식 작성에 의한 결과값으로 표기 하기 위해 "현재 재직중"이라는 텍스트 데이터 값을 입력한 것이고, 만약 사용자가 수식을 작성 할 경우 "현재 재직중"일 경우에는 퇴사일이 존재 하지 않기 때문에 공란 또는 공백으로 둘 경우 위 수식 =IF(D2="현재 재직중"을 =IF(D2=""으로 수정하여 사용 할 수 있습니다.
혹은 현재 재직중일 경우 현재 날짜 기준으로 경력기간 또는 재직기간을 계산 하는 방법은 결국 퇴사일을 현재 날짜라고 가정하고 계산하는것이기 때문에 재직중일 경우 퇴사일의 셀에는 =today() 수식을 작성하여 계속해서 현재 날짜 기준으로 퇴사일이 변경될 수 있도록 해줄수도 있겠습니다.
이렇게 현재 재직중일 경우에 =today()로 수식을 작성하게 된다면 위 수식을 작성해도 무관하겠지만 수식에서 필요 없는 계산 수식을 삭제하고 수식의 길이를 줄이고 싶다면 다음과 같이 작성 할 수 있습니다.
| =DATEDIF(C7,D7,"y")&"년"&DATEDIF(C7,D7,"ym")&"월"&DATEDIF(C7,D7,"md")&"일" |
사용자가 입력하는 데이터 형식이 여러 형식으로 작성될 수 있기 때문에 자신의 데이터 입력 형식에 따라 여러 형태로 수식을 변경하고 수정하는 방법에 대해서도 알아둘 필요가 있는것이겠죠.
2-2. "00년00월00일"과 같은 텍스트로 입력된 데이터 값의 날짜기간, 경력기간, 재직기간 합계 수식 작성.
E8셀에서는 E2셀부터 E7셀의 경력기간 또는 재직기간의 합계를 결과값으로 표기 하기 위한 수식을 작성해야 하는데 단순 sum함수는 숫자만을 합산할 수 있는 함수이기 때문에 "00년00월00일"과 같이 텍스트가 포함된 형식의 날짜기간, 경력기간, 재직기간의 합계를 결과값으로 표기 할 수 없습니다.
따라서 E8셀에 총 7군데의 경력기간 또는 재직기간의 합계를 한번에 계산하기 위한 수식은 다음과 같이 작성 할 수 있습니다.
| =DATEDIF(SUM($C$2:$C$7),SUM($D$2:$D$7),"y")&"년"& DATEDIF(SUM($C$2:$C$7),SUM($D$2:$D$7),"ym")&"월"& DATEDIF(SUM($C$2:$C$7),SUM($D$2:$D$7),"md")&"일" |
위와 같이 수식을 작성할 경우 D7셀에 작성된 "현재 재직중"이라는 텍스트 값으로 인해서 오류값을 반환하게 됩니다.
따라서 위의 수식을 작성할 경우에는 입사일과 퇴사일 사이에 텍스트로 작성된 데이터 값이 존재하면 안되기 때문에 "현재 재직중"의 셀 D7셀의 데이터 값은 =today()수식을 작성하여 현재 날짜로 데이터 값이 표기 되도록 수정 후에 위 수식을 작성해 주면 원하는 각 경력기간 또는 재직기간의 합계를 결과값으로 표기 할 수 있게 됩니다.
합계함수 sum함수를 사용하기 위해서 E열에 수식으로 작성된 결과값을 참조하기에는 텍스트가 포함된 날짜기간 형식의 결과값이기 때문에 날짜로 표기 되어있지만 표시형식을 날짜에서 일반으로 변경해 보면 각각의 날짜가 숫자로 변경되는것을 확인할 수 있습니다.
따라서 날짜로 표기된 데이터 값이지만 실제로는 각 날짜마다 고유의 숫자 데이터 값으로 변환되는 숫자 데이터를 표시형식에서 00-00-00과 같은 날짜 형식으로 변경하여 눈으로 보여지는 데이터 값을 날짜 형태로 보는것이고 실제로는 숫자 형태의 데이터 값을 갖는것이라고 할 수 있기 때문에 시작일자 또는 입사일자는 C열의 합계, 종료일자 또는 퇴사일자는 D열의 합계, 그리고 년, 월, 일의 각각의 결과값을 &기호로 연결하여 00년00월00일의 총 경력기간 또는 재직기간으로 합계를 계산 할 수 있는 수식을 작성합니다.
2-3. "00년00월00일"과 과 같은 텍스트로 입력된 데이터 값을 조건에 따라 날짜기간, 경력기간, 재직기간 합계 수식 작성.
2-2번에서는 각각의 경력기간, 재직기간의 총 합계를 계산 하는 수식을 작성해 보았는데 실제로 각각의 경력기간을 "00년00월00일"과 같이 텍스트가 포함된 날짜 기간의 형식으로 표기 했기 때문에 날짜를 숫자로 변경이 가능하고 숫자로 읽을 수도 있는 날짜 C열과 D열을 참조 하여 sum함수로 총 날짜 기간, 경력기간, 재직기간의 합계를 계산 하는 수식을 작성해 보았습니다.
여기에 더해서 각 부서마다 근무한 총 근무 경력기간, 재직기간, 날짜기간의 합계를 계산하기 위해서는 각 외식사업부 2군데에서 근무한 총 경력기간, 재직기간과 같이 동일한 부서에서 근무한 경력기간 또는 재직기간만을 합산하기 위한 조건이 있는 합계를 계산해야 합니다.
조건이 있는 합계는 sumif함수, 다중 조건 합계는 sumifs함수를 사용해야 한다는것이 다를뿐 sum함수와 같이 텍스트가 포함된 데이터 값은 합산할 수 없기 때문에 E열의 합계 자체를 계산할 수는 없습니다.
따라서 조건이 있는 합계라도 텍스트를 포함한 데이터 값을 합산할수는 없기 때문에 C열과 D열을 참조해서 합계를 계산하는 수식을 작성해야 하기 때문에 외식사업부에서의 경력기간 합계, 경영지원에서의 경력기간 합계, 생산관리에서의 경력기간 합계는 다음과 같이 E9셀, E10셀, E11셀에 각각 작성 할 수 있습니다.
| ① E9셀 / 외식사업부 재직기간 합계 수식. =DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"외식사업부"), SUMIFS($D$2:$D$7,$B$2:$B$7,"외식사업부"),"y")&"년"& DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"외식사업부"), SUMIFS($D$2:$D$7,$B$2:$B$7,"외식사업부"),"ym")&"월"& DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"외식사업부"), SUMIFS($D$2:$D$7,$B$2:$B$7,"외식사업부"),"md")&"일" ② E10 / 경영지원 부서 재직기간 합계 수식. =DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"경영지원"), SUMIFS($D$2:$D$7,$B$2:$B$7,"경영지원"),"y")&"년"& DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"경영지원"), SUMIFS($D$2:$D$7,$B$2:$B$7,"경영지원"),"ym")&"월"& DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"경영지원"), SUMIFS($D$2:$D$7,$B$2:$B$7,"경영지원"),"md")&"일" ③ E11 / 생산관리 부서 재직기간 합계 수식. =DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"생산관리"), SUMIFS($D$2:$D$7,$B$2:$B$7,"생산관리"),"y")&"년"& DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"생산관리"), SUMIFS($D$2:$D$7,$B$2:$B$7,"생산관리"),"ym")&"월"& DATEDIF(SUMIFS($C$2:$C$7,$B$2:$B$7,"생산관리"), SUMIFS($D$2:$D$7,$B$2:$B$7,"생산관리"),"md")&"일" |
위 수식을 보면 모두 동일한 수식이지만 다중조건합계 함수 sumifs의 조건인 "외식사업부", "경영지원", "생산관리"의 조건만 달리 했을뿐 모두 동일한 수식이라고 볼 수 있습니다.
또한 2-2번에서 sum함수의 참조 범위 C열과 D열로 결과값을 계산한것과 같이 sumifs함수에서도 C열과 D열을 참조 범위 영역으로 해서 결과값을 계산하는것을 볼 수 있고, 기본적인 수식 구조는 단일 경력기간 또는 단일 재직기간, 단일 날짜기간을 계산하는 결과값을 계산하는 수식 구조와 동일한 구조 형태인것을 확인해 볼 수 있습니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"텍스트가 포함된 경력기간 또는 날짜기간 합계 수식 작성" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
※ 텍스트가 포함된 경력기간 또는 날짜기간 합계 수식 작성 샘플 예제 다운로드.
'엑셀(Excel)' 카테고리의 다른 글
| 엑셀(Excel) 근속 기간에 따라 수당 차등 지급 계산 수식 작성을 해보자. (0) | 2022.11.24 |
|---|---|
| 엑셀(Excel) 정해진 데이터 값 이외 데이터 값 입력 방지 드롭다운 목록 만들기 엑셀 데이터 유효성 검사 기능을 사용해 보자. (0) | 2022.11.24 |
| 엑셀(Excel) 급여대장 서식과 공제액 계산 수식을 작성해 보자. (0) | 2022.11.22 |
| 엑셀(Excel) 시작일자(날짜)로부터 종료일자(날짜)까지 경과일 계산 하는 수식을 작성해 보자. (0) | 2022.11.22 |
| 엑셀(Excel) 주민 등록 번호로 나이 계산 수식 작성을 해보자. (0) | 2022.11.21 |
댓글