날짜와 시간을 한 셀에 넣어 주말과 공휴일을 제외한 작업 일시를 구하는 방법

안녕하세요.

 

질문을 받았습니다.

 

날짜와 시간을 같은 셀에 넣어서 주말과 공휴일 제외해서 TURNAROUND TIME 구하는거

지혜를 주세요~networkdsays intl 사용하는것은 알고 있는데요... 시간과 날짜를 한 셀에 넣어서 구하는 방법은 어떻게 하면 좋을지요?

감사합니다.

 

 

선생님,

날짜와 시간이 별도로 있는 상황에서도 토요일 일요일 제외하고 공휴일 제외하고 turnaround 타임 구하는 방법 부탁드립니다.

감사합니다.

 

예시용으로 만든 파일을 만들어 첨부해 주셔서 문제를 이해하는데 큰 도움이 되었습니다. ^^

 

완성 파일 다운로드

완성파일.xlsx

0.01MB

 

 

질문에서 미리 이야기했듯이 아이디어는 간단합니다.

작업한 날짜 수에서 NETWORKDAYS.INTL 함수를 이용해서 휴일에 해당하는 날짜 수를 빼는 겁니다.

 

그런데 언제나 그렇듯이 문제를 해결하다 보면 간단하지 않다는 것을 알게 됩니다.

 

 

원래 내용은 유지하고 이것 저것 값을 바꿔보기 위해 시트를 복사했습니다.

 

첫번째 질문인 날짜와 시간이 각각 다른 셀에 있는 것을 하나의 셀로 합치는 것은 간단합니다.

엑셀은 날짜와 시간을 숫자 값으로 다룹니다.

하루는 1이고 시간은 소수값으로 나타냅니다. 12시는 0.5, 저녁 6시 0.75입니다.

 

작업 시트 [B36] 셀에 [B22] 셀 날짜와 [C22] 셀 시간을 합쳐 나타나도록 수식을 작성했습니다.

=B22+C22

 

 

나머지 셀도 수식을 복사해서 값을 나타냅니다.

 

[D36] 셀에 시작 일시와 완성 일시의 차를 계산하는 수식을 작성합니다.

=C36-B36

 

[E36] 셀에 시간을 제외한 날짜만 구합니다.

=DATEDIF(B36,C36,"d")

 

[F36] 셀에 NETWORKDAYS.INTL 함수를 이용해 두 날짜 사이의 휴일을 제외한 날짜 수를 구합니다.

=NETWORKDAYS.INTL(B36,C36,1,$B$7:$B$15)

 

[G36] 셀에 날짜에서 휴일을 제외한 날짜를 빼고, 날짜를 계산하는 방식의 차이 때문에 생기는 차이를 보정하기 위해 1을 더하는 수식을 작성합니다.

=E36-F36+1

 

DATEDIF 함수로 2024-08-05과 2024-08-06의 차를 구하여 1이 나옵니다.

NETWORKDAYS.INTL 함수를 이용하면 작업 첫째날, 작업 둘째날로 계산해서 2가 나옵니다.

그래서 두 날짜 사이의 차를 구해야 하므로 NETWORKDAYS.INTL 함수의 결과에서 1을 빼서 결과값을 보정합니다.

 

[H36] 셀에는 혹시 계산값이 0보다 적은 경우 결과가 '########' 모양으로 날짜를 나타낼 수 없다는 값이 표시되므로 IF 함수로 0이 나오도록 수식을 작성합니다.

=IF(D36-G36>0,D36-G36,0)

 

 

문제를 해결할 수식을 한 번에 작성하지 않고 나눠서 만들면 덜 복잡해서 해결 방법을 찾기 쉽습니다.

 

이렇게 구한 수식을 모두 합쳐 완성된 수식을 [D31] 셀에 작성합니다.

=IF(C31-B31-(DATEDIF(B31,C31,"d")-NETWORKDAYS.INTL(B31,C31,1,$B$7:$B$15)+1)>0,C31-B31-(DATEDIF(B31,C31,"d")-NETWORKDAYS.INTL(B31,C31,1,$B$7:$B$15)+1),0)

 

[D31:D34, H36::39] 셀은 <Ctrl +1>을 눌러 셀 서식에서 사용자 지정 서식 d일 hh:mm을 적용해서 나타냅니다.

 

 

 

예시 자료로 테스트를 해 보니 2가지 문제가 있습니다.

 

[D31], [D32] 셀 결과가 0인데 [D31] 셀은 3분의 작업 시간이 있지만 2024-08-04가 일요일이기 때문에 0으로 되는 것이 올바릅니다.

[D32] 셀은 2024-08-05가 Holiday로 지정되어 있어 2024-08-06에 작업한 09:51이 작업 시간으로 나타나야할 듯 한데 이것까지 수식으로 구현하기 복잡해서 그냥 0으로 처리되었습니다.

작업 일수가 2일 초과인 경우에는 제대로 계산됩니다.

결과가 0으로 나타난 셀은 사람이 확인을 한 번 하는 것으로 복잡한 수식을 작성하지 않고 해결책을 제시합니다.

 

두번째 문제는 사용자 지정 서식 문제입니다.

작업일이 32일이면 결과로 표시되는 값은 '1일'이 나타납니다.

자리 올림이 생겨 1달이 생략되어 나타나기 때문입니다.

이 문제를 해결하려면 날짜와 시간을 분리해서 날짜를 숫자로 바꿔 나타내고 뒤에 시간을 붙여야 하는데, 이것도 구현하려면 너무 복잡해서 생략합니다.

 

 

엑셀은 날짜와 시간을 숫자로 나타내기 때문에 다양한 계산이 가능합니다.

 

필요한 분께 도움이 되길 바랍니다.