근속기간(날짜) 정렬 문제

1. 문제

근속시간 정렬(문제).xlsx

0.01MB

 
아래와 같이 DateDif 함수를 이용해 근속 기간을 구한 후
G7에 =DATEDIF(D7,E7,"Y")&"년 " & DATEDIF(D7,E7,"ym") & "개월 "&DATEDIF(D7,E7,"md") & "일 "라고 입력하고, 아래로 복사한 후 

 
순위를 =RANK(G7,$G$7:$ G$20,0)를 이용해 구하면 #VALUE! 에러가 발생합니다.

 
원인은 근속 기간이 숫자가 아니라 문자(텍스트) 형식이라 그렇습니다. 이 상태에서는 표시 형식을 날짜라고 지정해도 텍스트 형식이 변경되지 않습니다.
 

2. 해법

가. 텍스트가 아닌 날짜를 반환하도록 수식  변경
반환 값을 텍스트 형식에서 날짜 형식으로 바꾸면 됩니다.
 
이 때 Date 함수를 이용하는데, 위에서 구한 연, 개월, 일을 Date함수의 year, month, day로 입력하고, 년, 개월, 일이란 글자는 지웁니다.
 
그러면 G7의 수식은 =DATE(DATEDIF(E7,F7,"Y"), DATEDIF(E7,F7,"ym"),DATEDIF(E7,F7,"md"))가 됩니다.
수식을 바꾸자마자 오른쪽에 순위가 1로 표시됩니다.

 
 
나. 표시 형식 변경
현재는 연-월-일로 표시되는데, ?연 ?개월 ?일로 바꿔보겠습니다.
 
표시 형식 - 사용자 지정에서 yyyy-mm-dd를 선택한 후
yy년 mm개월 dd일로 수정하면 됩니다.
 
G7셀부터 G20셀까지 선택한 다음
yy년 mm개월 dd일로 수정합니다.
그러면 그위 '보기'에 46년 07개월 21일로 표시됩니다.

 
07개월을 7개월로 표시하려면 m개월로 수정하면 되는데,
년은 y년으로 수정해도 2자리로 표시됩니다.
 
다. 수식 복사
G7셀의 수식을 G20셀까지 복사합니다.
 
이 때 G7셀의 채우기 핸들을 G20셀까지 끌면
'병합하려는 모든 셀의 크기가 동일해야 합니다.'란 에러 메시지가 표시되므로 안됩니다.

 
 
따라서, G7셀의 수식을 복사한 후 G8셀부터 G20셀까지 마우스로 끌어서 범위를 선택한 후 붙여 넣습니다.
G9셀의 셀 병합이 자동으로 풀리고, G10셀은 왼쪽 E10, F10셀의 값이 비어 있으므로( E9셀과 E10셀이 병합되면 값은 E9셀에만 있습니다) #NUM! 에러가 표시되고 순위가 모두 #NUM!가 됐으므로 G10셀의 값을 지워 버립니다. 

 
그러면 순위가 모두 제대로 표시됩니다.

 
라. 셀 병합
이제 G9셀과 G10셀을 병합해도 순위에 변화가 없습니다.

근속시간 정렬(완성).xlsx

0.01MB