엑셀, 재고관리 - 유통기한, 재점검일자 확인용

A열에 날짜(유효기간)가 입력돼 있을 때, “오늘 기준으로 한 달 남은 날부터 만료 전까지” 빨간색으로 바꾸고 싶다면 조건부 서식에 다음 수식을 사용하면 된다.

 

=AND(ISNUMBER($A1), TODAY()>=EDATE($A1,-1), TODAY()<$A1)

• A1 → 첫 번째 날짜가 들어 있는 셀 주소로 바꿔 사용

아래는 전체 설명을 자세히 정리해 보았다.

참고) Today() 를 오늘 날짜인 2025-12-02로 계산한다.

---

1. 상황 정리

• A열에 제품의 유효기간(만료일) 이 날짜 형식으로 들어 있다.
• 유효기간이 딱 한 달 남은 시점부터 만료일까지 자동으로 빨간색 표시를 하고 싶다.
• 예를 들어, 만료일이 2025-12-01 이라면 2025-11-01 ~ 2025-11-30 사이에만 빨간색이 되도록 만드는 것이다.

이럴 때 가장 깔끔한 방법이 조건부 서식 + 수식 조합이다.

---

2. 조건부 서식 설정 단계

예시는 A3부터 아래로 날짜가 있다고 가정하겠다. (첫 날짜 셀 주소만 본인 파일에 맞게 바꾸면 된다.)

1. 범위 선택
   • A열에서 날짜가 들어 있는 구간을 드래그
     예: A3:A1000
2. 조건부 서식 메뉴 열기
   • 상단 리본 메뉴에서
     [홈] → [조건부 서식] → [새 규칙] 클릭
3. 수식을 사용하여 서식을 지정할 셀 결정 선택
   • “서식 규칙 편집” 창에서
     「수식을 사용하여 서식을 지정할 셀 결정」 항목 클릭
4. 수식 입력
   • “다음 수식이 참인 값의 서식 지정” 칸에 다음 수식을 입력한다.
   =AND(ISNUMBER($A1), TODAY()>=EDATE($A1,-1), TODAY()<$A1)

    
   ⚠️ 첫 데이터가 A6이라면 $A6, A7이라면 $A7 처럼 첫 행 기준으로 바꿔 써야 한다.
        다만, $A1로 동적범위를 설정할 경우 첫 데이터 위치와 상관없이 범위만 일치시켜도 적용된다.
5. 서식 지정
   • 오른쪽의 [서식] 버튼 클릭
   • [채우기] 탭에서 빨간색 선택
   • 필요하면 글꼴 색은 흰색으로 변경
   • 확인 → 다시 확인

이렇게 하면 선택한 범위 안에서, 유효기간이 한 달 이내로 다가온 날짜 셀이 자동으로 빨간색으로 바뀐다.

 

 

 

---

3. 수식 구조 설명

입력한 수식을 하나씩 뜯어보면 왜 이렇게 되는지 이해가 쉽다.

 

=AND( ISNUMBER($A1), TODAY()>=EDATE($A1,-1), TODAY()<$A1 )

1. ISNUMBER($A1)
   • A1 셀이 진짜 날짜(숫자) 인지 확인
   • 날짜가 아닌 빈 셀, 텍스트 등은 제외하려고 넣는 안전장치
2. EDATE($A1,-1)
   • A1의 날짜에서 캘린더 기준 한 달 전을 계산
   • 예: A1가 2025-12-01이면 EDATE(A1,-1) = 2025-11-01
3. TODAY()>=EDATE($A1,-1)
   • 오늘 날짜가 “만료일 한 달 전” 이후인지 확인
   • 즉, 한 달 전이 지나야 TRUE
4. TODAY()<$A1
   • 오늘 날짜가 아직 만료일 이전인지 확인
   • 만료일이 지난 것은 다른 규칙(예: 회색 처리, 진한 빨강 등)으로 따로 관리하고 싶을 때 유용
5. AND( … )
   • 위 조건이 모두 만족될 때만 TRUE
   • 결국 “날짜가 입력돼 있고, 오늘이 만료일 한 달 전 이상이면서 만료일 전인 경우”에만 셀 서식을 적용하게 된다.

---

4. 응용: 30일 기준으로 하고 싶다면?

“달 단위” 말고 정확히 30일 이내를 보고 싶다면 수식을 이렇게 바꿔도 된다.

 

=AND( ISNUMBER($A1), $A1-TODAY()<=30, $A1>=TODAY() )

• A1 - TODAY() <= 30 → 남은 일수가 30일 이하
• $A1>=TODAY() → 이미 지난 날짜는 제외

---

5. 자주 나오는 오류 체크 포인트

1. 텍스트 날짜일 때
   • 셀에 2025-12-01 처럼 보이지만 실제로는 텍스트인 경우
   • 가운데 정렬 + 왼쪽 작은 초록 삼각형이 뜨면 거의 텍스트다.
   • DATEVALUE, 텍스트 나누기, “.” → “-” 바꾸기 등으로 진짜 날짜로 변환한 뒤 위 수식을 적용해야 한다.
2. 행 번호 고정 여부
   • 수식 안에서 $A1 처럼 **열은 $A, 행은 1**로 쓰는 이유
   • 기준 행(A1)을 시작점으로, 아래 행들은 자동으로 A2, A3… 으로 밀려가도록 하기 위해서다.
   • $A$1로 써버리면 모든 행이 A1만 보게 되어 잘못 동작한다.

---

이렇게 설정해 두면 유효기간 관리 파일에서
“한 달 남은 항목만 자동으로 빨갛게 떠서 눈에 확 들어오는” 상태가 된다.

재고·계약 만료일·보험 갱신일 등 일정 관리에 그대로 응용할 수 있으니,
한 번 템플릿 만들어 두고 다른 파일에서도 복붙해서 쓰면 상당히 편하다.

조건부서식.xlsx

0.01MB