엑셀 수식을 작성해서 날짜와 날짜 사이의 경과일수, 경과월수, 경과년수를 계산 하는 수식을 작성하여 날짜 기간을 원하는 형태로 년, 월, 일수로 계산할 수 있습니다.
이러한 결과값을 가져오는 수식을 적용할 수 있는 자동 서식은 근무일수를 계산하거나 태어난 날짜를 기준으로 나이를 계산하는 서식, 날짜와 날짜 사이의 경과일을 계산하는 형태의 문서 서식에서 적용해 볼 수 있습니다.
| 엑셀 주민등록번호 나이 계산 수식. [샘플 서식 첨부] |
예제.
날짜 경과일 또는 날짜 기간을 계산하는 수식을 작성해 보기 위해서 B열 입사일, C열은 퇴사일을 작성했고 퇴사일자를 작성하지 않았다면 재직중인것으로 가정하고 D,E,F,G열에 각각 입사일자와 퇴사일자간의 기간이 입력될 수 있도록 수식을 작성합니다.
이 글을 작성하는 일자는 2024-01-02일로 C열 퇴사일자가 공란인곳은 2024-01-02로 결과값이 계산된다는점 참고 해 주시면 됩니다.
날짜 기간 계산 수식.
D열은 근무년수, E열에는 근무 개월수, F열 근무 일수를 계산 하는 수식을 작성하고 G열은 근무일수를 “00년00월00일”로 표기 하는 수식을 작성해 보도록 합니다.
근무 년수 계산 수식.
| =IF(C2=””,DATEDIF($B2,TODAY(),”Y”),DATEDIF($B2,$C2,”Y”)) |
근무 년수를 계산 하는 수식은 DATEDIF($B2,TODAY(),”Y”)으로 현재날짜-입사날짜로 근무 년수를 계산할 수 있습니다.
단 datedif함수 수식 구문에 따라 근무 년수를 계산하는 것으로 표시형식은 “Y”로 인수를 작성합니다.
그러나 C4행에는 퇴사일자가 입력되어 있기 때문에 근무 기간의 년수를 계산하기 위해서는 현재날짜-입사날짜로 계산하지 않고 퇴사일자-입사일자로 근무 기간에 대한 년수를 계산해야 하므로 참조 하는 인수는 Today()대신에 C2셀을 인수로 작성해야 합니다.
따라서 if함수로 수식을 작성하여 C행이 공란이라면 현재날짜-입사일자로 계산하고 C행이 공란이 아니라면 C행일자-입사일자로 계산하기 위해 위와 같은 수식으로 작성할 수 있습니다.
근무 개월수 계산 수식.
| =IF(C2=””,DATEDIF($B2,TODAY(),”YM”),DATEDIF($B2,$C2,”YM”)) |
근무 년수와 근무 개월수 계산 수식의 차이는 datedif수식 구문중 표시형식에 차이만 있을뿐 구문은 동일하게 작성할 수 있습니다.
년수를 계산하기 위해서는 datedif함수 수식 구문의 표시형식을 “Y”로 인수를 작성하지만 개월수의 경우에는 “YM”으로 작성하면 년차를 뺀 나머지 개월수만을 계산합니다.
예를 들어 10년 8개월의 총 개월수는 128개월이지만 120개월을 뺀 나머지 개월수 8개월만을 계산할 수 있습니다.
만약 총 개월수로 표기하고자 한다면 datedif함수의 표시형식 인수를 “M”으로 작성하면 총 개월수를 계산하여 반환할 수 있습니다.
근무 일수 계산 수식.
| =IF(C2=””,DATEDIF($B2,TODAY(),”MD”),DATEDIF($B2,$C2,”MD”)) |
일수를 계산 하는 수식 역시 근무 년수와 개월수를 계산하는 수식에서 datedif함수의 표시형식 인수를 “MD”로 하면 년수와 개월수를 뺀 나머지 일수만을 계산할 수 있습니다.
총 개월수를 계산하는것과 같이 총 일수를 계산하고자 한다면 표시형식은 “D”로 표시형식 인수를 수정해 주게 되면 총 일수를 계산할 수 있습니다.
경과 날짜 “00년00월00일”로 결과값 가져오는 수식.
위에서 퇴사일자-입사일자의 날짜 기간을 계산 하는 수식으로 근무년수, 개월수, 일수를 계산 하는 수식으로 각각 작성해 보았습니다.
이렇게 각각 계산한 결과값을 “00년00월00일”과 같은 표시형식으로 결과값을 반환할수도 있습니다.
| =IF(C2=””,DATEDIF($B2,TODAY(),”Y”),DATEDIF($B2,$C2,”Y”))&”년”& IF(C2=””,DATEDIF($B2,TODAY(),”YM”),DATEDIF($B2,$C2,”YM”))&”월”& IF(C2=””,DATEDIF($B2,TODAY(),”Md”),DATEDIF($B2,$C2,”Md”))&”일” |
위에서 결과값을 반환하는 각각의 수식을 텍스트를 포함하는 결과값으로 이어주기 위해서 &(앰퍼스트) 기호로 근무년수, 개월수, 일수의 수식을 하나의 수식으로 이어줄 수 있습니다.
&(앰퍼스트) 기호는 일반적인 기호로 사용할때 앤드와 같은 개념으로 “X&Y”와 같이 사용되는데 엑셀에서도 앤드와 같은 개념이면서 문장을 서로 연결시켜주는 기능으로 작동합니다.
따라서 각각의 결과값을 반환하는 수식을 하나의 값으로 만들어 주거나 수식의 결과값에 텍스트 또는 규칙적인 값을 나타내고자 할때 &(앰퍼스트) 기호를 사용할 수 있습니다.
글을 마치며…
엑셀에서 시간, 날짜와 같은 데이터값의 표시형식을 일반 또는 숫자로 변경하게 되면 일련의 숫자값으로 바뀌게 되는데 이렇게 변경되는 숫자값을 시간 또는 날짜 데이터를 일련의 코드 넘버로 데이터값을 처리 하고 있는것으로 생각해 볼 수 있습니다.
따라서 숫자의 사칙연산중 빼기 연산으로 시간 또는 날짜와 같이 -(마이너스)값을 갖지 않는 데이터값의 연산은 큰숫자-작은숫자로 계산해줘야겠죠.
시간의 경우에는 큰시간-작은시간으로 계산 후 표시형식을 일반으로 수정해 주게 되면 시간과 시간사이의 값을 계산해주게 되는데 날짜의 경우에는 단순 사칙연산으로 원하는 결과값을 반환하지 않게 됩니다.
그 이유로는 시간은 24시간을 1로 환산하기 때문에 사칙연산으로 계산이 되지만 날짜의 경우에는 1900-01-01의 날짜를 1로 시작하여 계산하고 그 이전 날짜인 1899-12-31이전의 날짜는 숫자로 반환되지 않기 때문에 단순한 사칙연산으로는 날짜와 날짜 사이의 기간을 계산할 수 없게 됩니다.
이렇게 날짜의값이 1900-01-01날짜 이전의값은 숫자로 변환되지 않고 날짜값을 유지하면서 1900-01-01 이후의 날짜값만 숫자로 변환되는 이유를 찾기 위해서는 엑셀이 보급되던 시점을 생각해 봐야 할텐데 컴퓨터가 보급되던 초기 DOS위주를 사용하던것에서 Wimdows체제로 넘어오면서 부터 본격적으로 엑셀(EXCEL)이 주류를 이루면서 기존의 DOS체제를 Windows체제에 맞는 인터페이스로 진화하면서 개발되었기 때문으로 추측해 볼 수 있습니다.
이러한 이유들로 인해서 시간 계산은 단순 사칙연산으로 계산하되 작은시간에서 큰 시간을 빼야 하는 경우는 +1을 해주고 날짜의 경과시간을 계산하기 위해서는 datedif함수 수식으로 계산하여 결과값을 반환할 수 있습니다.
