# How to calculate the next billing date for a monthly subscription in Excel

Count the months billed so far and step one more: `=EDATE(B2,DATEDIF(B2,$F$1,"m")+1)`, with today in F1.

**Excel:** `=EDATE(B2,DATEDIF(B2,$F$1,"m")+1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Customer | Start date | Next billing |  | Today | 2026-10-29 |
| Ana | 2026-01-15 |  |  |  |  |
| Ben | 2025-08-31 |  |  |  |  |
| Cleo | 2026-07-29 |  |  |  |  |
| Dev | 2024-03-01 |  |  |  |  |
| Elle | 2026-02-28 |  |  |  |  |

Formula in C2, filled to C6: `=EDATE(B2,DATEDIF(B2,$F$1,"m")+1)`

Result: 11/15/2026; 10/31/2026; 11/29/2026; 11/1/2026; 11/28/2026

## How it works

`DATEDIF(B2,$F$1,"m")` counts the complete months since the start. One more month, added with EDATE, is the next renewal. EDATE keeps month-end plans at the month end: Ben started on August 31, so he is billed on October 31. Cleo is billed on the 29th, today, so her next date is November 29.

## Other ways

- `=EDATE(B2,(YEAR($F$1)-YEAR(B2))*12+MONTH($F$1)-MONTH(B2)+(DAY($F$1)>=DAY(B2)))`: The same without DATEDIF: count the calendar months, and add one if this month's billing day has passed.

## Common mistakes

- Adding 30 days per month drifts: a plan that started on January 15 would bill on March 16 instead of March 15.
- If the start date is after today, DATEDIF returns #NUM!. Use `=IF(B2>$F$1,B2,EDATE(…))`.

## Questions

**How do I handle yearly plans?**

Count whole years and step by 12 months: `=EDATE(B2,12*(DATEDIF(B2,$F$1,"y")+1))`.

**How many billing cycles have there been?**

`=DATEDIF(B2,$F$1,"m")+1`, counting the first charge on the start date.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/next-billing-date