Date FunctionsIntermediate

Work Out "End of Next Month" Payment Due Dates with EOMONTH

A supplier's "due at the end of the month after invoice" terms aren't a fixed number of days — they're a calendar position, and EOMONTH lands on that position directly instead of guessing at it with +30.

Task:

You run accounts payable for a small manufacturer. One supplier bills on EOM+1 terms: every invoice is due on the last day of the month after the month it was issued in, however early or late in that month it was dated. In column C, use EOMONTH to find each invoice's due date. In column D, find how many days are left until it's due, counting from the report date in B1 — a negative number means the invoice is already overdue. Fill in rows 3 through 7.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

7 rows × 4 columns10 cells you fill in
ABCD
1Report date:10/27/2026
2InvoiceInvoice DateDue DateDays to Due
3INV-44178/31/2026
4INV-44529/3/2026
5INV-44869/30/2026
6INV-450110/1/2026
7INV-452310/19/2026
What this exercise teachesMay contain the answer

EOMONTH(B3,1) never counts days at all: it steps one month forward from the invoice's month and returns that month's last day, which is why INV-4452, dated September 3, and INV-4486, dated September 30, both come due on October 31 — on these terms they're the same invoice month, and four weeks of difference in their dates changes nothing. The +30 a quick reader reaches for gets INV-4452 badly wrong, landing on October 3 and chasing a payment 28 days before the supplier actually expects it. INV-4501 is the other side of that boundary: one day later than INV-4486, on October 1, it falls into the next invoice month and picks up a full extra month to pay, due November 30. Because EOMONTH works in calendar months, it also gets month lengths right for free — August 31 steps to September 30, not to a September 31 that doesn't exist. Subtracting $B$1 then turns each due date into days remaining, and INV-4417's -27 is the one row already past its September 30 deadline; the dollar signs keep every row measuring from that same report date rather than sliding down into the invoice list.