엑셀 수금예정일 날짜 밀릴 때: 거래처별 결제조건·월말 청구 SUMIFS 검산법

엑셀에서 수금예정일이 하루씩 밀리거나, 월말 청구 건이 다음 달로 넘어가서 미수금 합계가 안 맞는 경우가 꽤 많습니다. 특히 거래처마다 “익월 15일”, “익월 말일”, “익익월 10일”처럼 결제조건이 다르면 단순히 청구일에 30일을 더하는 방식으로는 금방 틀어집니다.

이번 글은 실무에서 자주 보는 매출채권 관리표를 기준으로, 거래처별 결제조건을 찾아 수금예정일을 계산하고, 기준월별 미수금 합계를 SUMIFS로 검산하는 흐름으로 정리해 보겠습니다. VLOOKUP, INDEX, MATCH, IF, IFERROR, DATE, EOMONTH, TEXT, ROUND처럼 많이 쓰는 함수만 엮어서 해결합니다.

어떤 상황에서 수금예정일이 틀어질까?

예를 들어 매출내역 시트의 A열부터 O열까지 아래처럼 자료가 들어온다고 보겠습니다. 실제 파일에서는 2행부터 5000행까지 데이터가 있다고 가정합니다.

오류가 나는 원인은 대체로 세 가지입니다. 첫째, A열의 날짜가 실제 날짜가 아니라 텍스트입니다. 둘째, 거래처코드 앞뒤에 공백이 있거나 마스터에 없는 코드입니다. 셋째, “31일 결제”를 DATE 함수로만 계산해서 2월, 4월, 6월처럼 말일이 짧은 달에서 날짜가 다음 달로 넘어갑니다.

먼저 날짜가 진짜 날짜인지 확인했나요?

A2:A5000에 들어온 세금계산서일이 2026.07.31 또는 2026-07-31처럼 보이더라도, 엑셀이 날짜로 인식하지 못하면 SUMIFS 날짜조건이 0으로 나옵니다. B2에는 계산용 날짜를 만들고 아래 수식을 입력한 뒤 B5000까지 내려 채웁니다.

=IFERROR(IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))),"날짜확인")

이 수식은 A2가 이미 날짜이면 그대로 쓰고, 텍스트이면 LEFT, MID, RIGHT로 연·월·일을 잘라 DATE로 다시 조립합니다. 만약 B열에 날짜확인이 뜬다면 A열 값이 20260731, 07/31/2026처럼 파일마다 다른 형식일 수 있으니 원본 패턴부터 확인해야 합니다.

거래처별 결제조건표는 어디에 둬야 할까?

같은 시트 오른쪽에 마스터를 둔다고 가정하겠습니다. Q2:R100에는 거래처별 조건코드, T2:W10에는 조건코드별 계산 규칙을 둡니다.

I2에는 C열 거래처코드로 조건코드를 찾아옵니다. Microsoft 365 또는 Excel 2021 이상이라면 XLOOKUP을 써도 좋고, 기존 파일 호환을 생각하면 VLOOKUP도 충분합니다.

=IFERROR(VLOOKUP(C2,$Q$3:$R$100,2,FALSE),"조건없음")

이 수식에서 C2는 현재 행의 거래처코드, Q3:R100은 거래처별 조건표입니다. 결과가 조건없음으로 나오면 수식보다 먼저 C열 코드와 Q열 코드가 정확히 같은지 확인해야 합니다.

월말 결제조건은 DATE보다 EOMONTH로 잡아야 합니다

J2에는 수금예정일을 계산합니다. 핵심은 V열 결제일이 0이면 말일로 보고 EOMONTH를 쓰는 것입니다. 결제일이 31일인데 해당 월에 31일이 없을 때도 말일로 보정합니다.

=IFERROR(IF(INDEX($V$3:$V$10,MATCH(I2,$T$3:$T$10,0))=0,EOMONTH(B2,INDEX($U$3:$U$10,MATCH(I2,$T$3:$T$10,0))),IF(INDEX($V$3:$V$10,MATCH(I2,$T$3:$T$10,0))>DAY(EOMONTH(B2,INDEX($U$3:$U$10,MATCH(I2,$T$3:$T$10,0)))),EOMONTH(B2,INDEX($U$3:$U$10,MATCH(I2,$T$3:$T$10,0))),DATE(YEAR(EOMONTH(B2,INDEX($U$3:$U$10,MATCH(I2,$T$3:$T$10,0)))),MONTH(EOMONTH(B2,INDEX($U$3:$U$10,MATCH(I2,$T$3:$T$10,0)))),INDEX($V$3:$V$10,MATCH(I2,$T$3:$T$10,0)))))),"조건확인")

수식이 길어 보이지만 구조는 단순합니다. MATCH로 I2의 조건코드가 T열에서 몇 번째인지 찾고, INDEX로 가산월과 결제일을 가져옵니다. 결제일이 0이면 EOMONTH로 해당 월 말일을 반환하고, 결제일이 실제 말일보다 크면 역시 EOMONTH로 막습니다.

예를 들어 B2가 2026-01-31이고 조건코드가 M1D31이라면 단순 DATE 계산은 2026-03-03처럼 밀릴 수 있습니다. 위 방식은 2026-02-28로 잡히기 때문에 월말 결제 거래처에서 특히 안전합니다.

청구금액, 미수잔액, 상태까지 같이 검산하기

수금예정일만 맞아도 절반은 해결되지만, 월별 미수금 합계가 맞으려면 금액 열도 안정적으로 계산해야 합니다. H2 청구금액은 공급가액 F2와 부가세 G2를 더해 ROUND로 정리합니다.

=ROUND(F2+G2,0)

M열에 실제 입금액이 들어온다면 N2 미수잔액은 아래처럼 계산합니다. 입금액이 빈칸이면 0으로 보고 싶을 때는 IF를 넣어 줍니다.

=H2-IF(M2="",0,M2)

O2 상태는 완납, 연체, 예정으로 나누면 피벗 없이도 빠르게 필터링할 수 있습니다.

=IF(N2=0,"완납",IF(TODAY()>J2,"연체","예정"))

이때 J열에 조건확인 같은 텍스트가 남아 있으면 O열 수식도 흔들릴 수 있습니다. 실제 파일에서는 먼저 J열을 필터로 열어 오류 문구가 있는 행을 정리한 뒤 상태 계산을 내려가는 편이 좋습니다.

SUMIFS가 0으로 나오면 이 확인표부터 보세요

기준월별 수금예정 미수금을 집계해 보겠습니다. Z2에는 기준월을 실제 날짜로 2026-08-01처럼 입력합니다. AA2에는 특정 거래처코드를 입력하고, 전체 거래처를 보고 싶으면 별도 수식을 하나 더 두는 편이 실무에서 덜 헷갈립니다.

AB2에 해당 월 전체 미수잔액을 구하려면 아래 수식을 사용합니다.

=SUMIFS($N$2:$N$5000,$J$2:$J$5000,">="&$Z$2,$J$2:$J$5000,"<="&EOMONTH($Z$2,0),$O$2:$O$5000,"<>완납")

특정 거래처만 보고 싶다면 C열 조건을 추가합니다.

=SUMIFS($N$2:$N$5000,$J$2:$J$5000,">="&$Z$2,$J$2:$J$5000,"<="&EOMONTH($Z$2,0),$C$2:$C$5000,$AA$2,$O$2:$O$5000,"<>완납")

연체 건수도 같이 확인하면 집계표 검산이 쉬워집니다. AC2에는 오늘 기준으로 수금예정일이 지났고 미수잔액이 남은 건수를 구합니다.

=COUNTIFS($J$2:$J$5000,"<"&TODAY(),$N$2:$N$5000,">0")

날짜 텍스트가 너무 많다면 선택 범위만 VBA로 정리하기

ERP에서 내려받은 A열 날짜가 매번 텍스트로 들어온다면, 선택한 범위만 날짜로 바꾸는 간단한 매크로를 써도 좋습니다. 실행 전에는 반드시 파일을 복사해 두세요. 매크로 실행 후에는 일반적인 실행 취소가 되지 않으므로, 문제가 생기면 저장하지 않고 닫거나 백업 파일로 되돌리는 방식이 안전합니다.

아래 코드는 선택한 셀 범위에서 2026.07.31, 2026-07-31 형태의 값을 실제 날짜로 바꿉니다. 적용 범위는 사용자가 마우스로 선택한 셀만입니다.

Sub 선택범위_날짜텍스트_변환()
    Dim c As Range
    Dim v As String
    
    For Each c In Selection
        If Len(c.Value) >= 10 Then
            v = Replace(CStr(c.Value), ".", "-")
            If Mid(v, 5, 1) = "-" And Mid(v, 8, 1) = "-" Then
                c.Value = DateSerial(Left(v, 4), Mid(v, 6, 2), Mid(v, 9, 2))
                c.NumberFormat = "yyyy-mm-dd"
            End If
        End If
    Next c
End Sub

사용 순서는 간단합니다. A2:A5000처럼 원본 날짜 범위를 선택한 뒤 매크로를 실행합니다. 다만 원본값을 직접 바꾸는 방식이므로, 원본 보존이 필요한 파일에서는 B열 계산용일자를 만드는 수식 방식이 더 안전합니다.

적용 전에 확인할 것

수금예정일 계산은 단순 날짜 더하기가 아니라, 거래처별 약속을 날짜 규칙으로 바꾸는 작업입니다. 그래서 계산식보다 표 구조가 먼저 안정되어야 합니다. 세금계산서일은 실제 날짜인지, 거래처코드는 마스터와 정확히 일치하는지, 조건코드표에서 말일을 0처럼 명확한 규칙으로 관리하는지부터 확인해 보세요.

마지막으로 집계는 항상 한 번 더 검산하는 습관을 추천합니다. 기준월 Z2의 월초와 EOMONTH 기준 월말 사이에 J열 수금예정일이 들어오는지, N열 미수잔액이 0보다 큰지, O열 완납 제외 조건이 제대로 걸렸는지만 확인해도 SUMIFS 0 나옴이나 월별 미수금 차이는 대부분 잡힙니다.