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 want | Formula | Result |
|---|---|---|
| Next statement date | =EDATE(B2,1) | 18 November 2026 |
| Cycle starts | =B2+1 | 19 October 2026 |
| Cycle ends | =EDATE(B2,1) | 18 November 2026 |
| Payment due for the closed statement | =B2+C2 | 7 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.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


