Spreadsheets

EOMONTH and EDATE formulas for bill due dates in a sheet

Use EDATE and EOMONTH to roll bill due dates forward, handle the 31st and find the last day of a card cycle, with worked rent and credit card examples.

Editorial illustration for EOMONTH and EDATE formulas for bill due dates in a sheet

Use EDATE to move a date forward by whole months and EOMONTH to find the last day of a month. =EDATE(B2,1) gives the same day next month, and =EOMONTH(B2,0) gives the last day of B2's month. Both work in Excel and Google Sheets, and both return a date serial, so format the cell as a date.

How does EDATE roll a due date forward?

EDATE takes a start date and a number of months. If the target month is shorter, it returns that month's last day instead of spilling over. So a due date typed as 31 January becomes 28 February (29 in a leap year) after =EDATE(B2,1).

  • Next month: =EDATE(B2,1)
  • Same date three months on, for an insurance premium: =EDATE(B2,3)
  • Previous month: =EDATE(B2,-1)

The trap is chaining. If C2 is =EDATE(B2,1) and C3 is =EDATE(C2,1), a bill due on the 31st goes 31 Jan, 28 Feb, 28 Mar, 28 Apr, and it never comes back to the 31st. Anchor every row to the first date instead: =EDATE($B$2,ROW()-ROW($B$2)). Row 3 then adds one month to the original, row 4 adds two, and so on.

What if the due day is the 31st or the 30th?

Keep the due day in its own cell, say D2 = 31, and clamp it to the length of each month. For a month that contains the date in A2:

=MIN(DATE(YEAR(A2),MONTH(A2),$D$2),EOMONTH(A2,0))

DATE would roll 31 April over to 1 May, and MIN picks the smaller of that and the month-end, so you get 30 April. It is the same idea EDATE uses, just visible.

Worked example: rent on the 5th

Say rent is due on the 5th and you want the next upcoming date from today. EOMONTH(TODAY(),-1) is the last day of last month, so adding 5 gives the 5th of this month:

=IF(DAY(TODAY())<=5, EOMONTH(TODAY(),-1)+5, EOMONTH(TODAY(),0)+5)

On 3 November 2026 it returns 5 November 2026. On 12 November 2026 it returns 5 December 2026. For a countdown, put that date in C2 and use =C2-TODAY() in D2. Conditional formatting with the formula =AND($D2>=0,$D2<=3) highlights anything due in three days.

Worked example: a credit card cycle

Say the statement date is the 18th and your card gives a fixed number of days to pay. Read that number off your own statement; here it is 20. Put the last statement date, 18 October 2026, in B2 and the days in C2.

What you wantFormulaResult
Next statement date=EDATE(B2,1)18 November 2026
Cycle starts=B2+119 October 2026
Cycle ends=EDATE(B2,1)18 November 2026
Payment due for the closed statement=B2+C27 November 2026
Days left in this calendar month=EOMONTH(TODAY(),0)-TODAY()depends on today

Purchases on 19 October to 18 November land on the next statement, which is why the table treats the cycle as a range. The due date for a cycle always comes from the printed statement, so type the real figure rather than trusting a formula.

What can go wrong?

  • A date typed as text, such as 05.11.2026 with dots, will not be read as a date. Check with =ISNUMBER(B2).
  • EDATE and EOMONTH need a real date in the first argument. In older Excel versions a text date can error with #VALUE!.
  • A result showing a five-digit number is just the unformatted serial; apply a date format to the cell.
  • Weekends and holidays are not handled. If a due date falls on a Sunday, check whether your bank shifts it; a plain formula will not.

When a sheet stops being enough

These formulas cover a dozen bills well. They do not remind you on your phone or notice that a bill amount changed. SpendixAI Pro includes bills alongside budgets and savings goals, so a long list of recurring dues can live there instead of in date arithmetic.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports