엑셀 조건부서식, 마감일 색이 틀릴 때 확인할 것

엑셀 조건부서식에서 마감일 색이 틀리면 수식의 참·거짓과 색을 표시하는 범위를 나눠 확인한다. 연체까지 ‘3일 이내’ 색으로 잡히거나 빈 날짜가 빨갛게 변한다면 비교 조건이 부족할 수 있다. 수식이 맞는데 다른 색이 보일 때는 규칙 순서를 확인한다.

아래는 Windows 데스크톱 Excel의 메뉴를 기준으로 직접 만든 연습이다. 실제 업무 자료나 Excel 실캡처가 아니며, 안내도와 표의 값은 설명용 가상 데이터다. 웹·Mac 버전에서는 메뉴 배치와 규칙 관리 방식이 다를 수 있다.

엑셀 조건부서식에서 연체와 임박은 어떻게 나눌까?

연체는 기준일보다 이전이고, 임박은 기준일부터 정한 기간까지다. ‘마감일에서 기준일을 뺀 값이 3 이하’만 쓰면 음수인 연체도 포함된다. 날짜가 비었는지, 숫자로 저장됐는지, 이미 완료됐는지도 따로 판단해야 한다.

이번 예제는 기준일 당일부터 3일 뒤의 하루 전체까지를 임박으로 정한다. 기준일이 10월 20일이면 20·21·22·23일이 들어가므로 날짜는 네 개다. ‘3일 이내’라는 문구만으로 업무 기준을 정하지 말고 시작과 끝을 먼저 합의한다.

새 시트의 A1에는 업무, B1에는 마감일, C1에는 상태를 적는다. F1은 기준일, F2는 =DATE(2026,10,20)으로 입력한다. F2와 B열의 숫자 날짜는 날짜 형식으로 표시한다. 기준일을 고정했으므로 나중에 열어도 아래 정답과 비교할 수 있다.

3행의 작은따옴표는 날짜처럼 보이는 텍스트를 의도적으로 만드는 입력이다. 9행은 날짜에 시간이 섞인 경우다. 날짜만 표시하는 서식에서는 시간이 안 보일 수 있으므로 검산할 때 B9를 날짜·시간 형식으로 바꿔 확인한다.

엑셀 조건부서식으로 행 전체에 색을 넣는 순서

A2:C9를 선택하고 수식 규칙을 만든다. 첫 행은 2행으로 맞추고, 판단할 B·C열은 고정하되 행은 이동하게 둔다. Microsoft 조건부서식 안내의 수식 규칙과 참조 방식을 이 관리표에 적용한 것이다.

1. A2를 시작 셀로 A2:C9를 선택한다. 머리글과 F열 기준일은 포함하지 않는다.
2. 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정으로 들어간다.
3. 아래 연체 수식을 입력하고 서식 → 채우기에서 연한 빨강을 고른 뒤 확인한다.
4. 같은 범위에 임박 수식을 새 규칙으로 추가하고 연한 노랑을 지정한다.
5. 홈 → 조건부 서식 → 규칙 관리에서 두 규칙의 적용 대상이 모두 =$A$2:$C$9인지 확인한다.

연체 규칙:

=IFERROR(AND(ISNUMBER($B2),$B2<$F$2,$C2<>"완료"),FALSE)

임박 규칙:

=IFERROR(AND(ISNUMBER($B2),$B2>=$F$2,$B2<$F$2+4,$C2<>"완료"),FALSE)

$B2는 어느 열을 칠하든 같은 행의 B열을 보라는 뜻이다. $F$2는 모든 행이 같은 기준일을 보도록 열과 행을 모두 고정한다. $B$2로 쓰면 각 업무의 날짜를 살피지 않고 전부 B2만 보게 된다.

임박의 끝을 <=기준일+3 대신 <기준일+4로 잡은 이유는 시간이다. 23일 18시는 23일 0시보다 크지만 24일 0시보다는 작다. 이 조건이면 마지막 날의 시각까지 포함한다. 마감 시각 자체를 엄격히 관리하는 표라면 일 단위가 아닌 별도 시간 기준이 필요하다.

색을 보기 전에 TRUE와 FALSE를 검산했는가?

규칙 수식을 일반 셀에 넣으면 색과 관계없이 조건의 결과를 확인할 수 있다. D1에 연체 판정, E1에 임박 판정을 적고 D2·E2에 위 수식을 각각 넣은 뒤 9행까지 채운다. 여기의 TRUE는 해당 조건에 맞는다는 뜻이며 업무 데이터가 모두 올바르다는 보증은 아니다.

빨강은 4행 한 줄, 노랑은 5·6·9행 세 줄이다. 기존 일반 채우기나 표 스타일이 있다면 ‘추가 색 없음’인 셀에도 원래 색은 남을 수 있다. 색만 보지 말고 D·E열과 함께 대조한다.

ISNUMBER 공식 설명에 따르면 텍스트 숫자는 자동으로 숫자로 바뀌지 않는다. 따라서 B3는 날짜처럼 보이더라도 판정에서 제외한다. 표시 형식을 ‘날짜’로 바꾸는 작업만으로 실제 텍스트가 숫자 날짜로 변환됐다고 단정하면 안 된다.

ISNUMBER는 날짜 전용 검사가 아니다. 실수로 123을 넣어도 숫자라서 통과한다. 이 예제는 마감일 열에 정상적인 날짜를 넣었다는 전제에서 빈칸과 텍스트를 거르는 방식이다. 입력 범위와 허용 날짜를 관리하는 작업은 별도로 해야 한다.

IFERROR 공식 설명에 맞춰 수식 오류는 FALSE로 돌렸다. 오류를 고치는 기능은 아니므로 색이 없는 행도 =ISERROR(B2) 같은 보조 검사로 원본 오류를 살핀다. 상태에 ‘완료 ’처럼 공백이 붙으면 완료 제외 조건도 달라진다. 조건 분기 자체가 낯설다면 엑셀 IF 함수 설명을 함께 참고한다.

엑셀 조건부서식 수식은 맞는데 왜 다른 색이 보일까?

일반 셀 판정은 TRUE인데 원하는 색이 안 보이면, 규칙 관리에서 적용 범위와 겹치는 규칙을 확인한다. 엑셀 조건부서식은 같은 셀의 같은 서식이 충돌할 때 목록 위쪽 규칙이 우선한다. 글꼴과 채우기처럼 서로 충돌하지 않는 속성은 함께 표시될 수도 있다.

이번 연체·임박 규칙은 날짜 구간이 겹치지 않는다. ‘참이면 중지’를 켜야만 정답이 나오는 설계가 아니다. 반대로 이미 있는 ‘기준일+3 이하’ 규칙을 그대로 두면 연체도 참이 되어 두 색이 충돌할 수 있다. 순서로 가리기 전에 잘못된 범위를 먼저 고친다.

‘참이면 중지’는 해당 규칙이 참인 셀에서 아래 규칙의 평가를 멈추는 옵션이다. 데이터 막대·색조·아이콘 집합 규칙에서는 선택할 수 없으므로 모든 규칙에 적용하는 해결책으로 삼지 않는다. 규칙 목록이 안 보이면 표시 범위를 ‘이 워크시트’로 바꿔 확인한다.

실무에서 기준일을 바꾸고 다시 확인하는 법

검산을 끝낸 뒤 F2를 =TODAY()로 바꾸면 현재 날짜를 기준으로 판단한다. 고정 예제의 정답 색과 달라지는 것은 정상이다. Microsoft TODAY 안내처럼 날짜가 예상대로 갱신되지 않으면 파일 → 옵션 → 수식 → 계산 옵션 → 자동도 확인한다.

설정 후에는 ‘날짜가 지난 진행 건 하나’와 ‘그 일을 완료로 바꾼 경우’를 번갈아 시험한다. F2를 바꿨을 때 D·E열 판정도 함께 이동하는지 보면 색 문제와 계산 문제를 구분하기 쉽다. 날짜 일수 자체가 이상하다면 경과일·포함일·근무일 계산 차이부터 점검한다.

색을 없애려면 일반 채우기만 바꾸기보다 해당 조건 규칙을 확인한다. 다른 표까지 영향을 주는 시트 전체 규칙 지우기는 피하고, 작업할 범위를 정한 뒤 필요한 규칙만 관리한다. 중복값 표시가 목적이면 중복값 찾기와 제거 방법이 별도 질문에 맞는 안내다.

엑셀 조건부서식의 최종 확인은 ‘예쁜 색이 나왔는가’가 아니라 어떤 행이 왜 선택됐는가다. 기준일·날짜값·상태·적용 범위를 같은 가상표에서 대조하고, 색만으로 전달하지 않도록 상태 문구도 함께 남긴다.

출처

1. Microsoft — 조건부서식 수식·적용 범위·규칙 우선순위
2. Microsoft — IS 함수와 ISNUMBER의 숫자 판정
3. Microsoft — IFERROR 함수
4. Microsoft — TODAY 함수와 자동 계산