01 / 10February's Collection Missed 214 Members

Date FunctionsEOMONTHData Quality

How to Add Months to a Date in Excel With EDATE

214 Gym Members Paid Nothing in February and Twice in March, Because the Billing Sheet Added a Month With DATE and Day 31 of February Is the 3rd of March

7 Nov 20269 min read

UsesEOMONTHDATEYEAR, MONTH, DAYMIN, MAX

Ashcombe Leisure charges each member £42 on the day of the month they joined. In February, 214 January joiners were not charged at all. In March they were charged twice, because one date formula had moved February's charge into March.

January joinersMembersFebruary charge dated
Joined 29 Jan6101/03/2026
Joined 30 Jan6802/03/2026
Joined 31 Jan8503/03/2026
Total214£8,988 not collected

Scope. EDATE, EOMONTH, DATE, YEAR, MONTH, DAY, MIN and COUNTIF work the same in every version of Excel since 2007, on Windows, Mac and the web. Nothing here needs Microsoft 365.

Throughout, the sample members are in A1:H8: member in A, join date in B, and four ways of dating the next charges in C to G. Column H checks the DATE column. Dates are shown day first.

01February's Collection Missed 214 Members

Ashcombe's direct debits run every morning and collect each charge dated that day. The dates come from one column of the billing sheet, which adds a month to the join date:

=DATE(YEAR(B2),MONTH(B2)+1,DAY(B2))

Seven January Joiners, Four Ways to Add a Month

Seven of Ashcombe Leisure's January 2026 joiners, in A1:H8. Column B is the join date. C, D and E date the February charge three ways: adding 30 days, DATE with the month plus one, and EDATE. F dates March by chaining EDATE off column E; G counts two months from the join date. H checks whether the DATE charge in column D falls in February.

ABCDEFGH
1
Member
Joined
Feb charge (+30 days)
Feb charge (DATE)
Feb charge (EDATE)
Mar charge (chained)
Mar charge (anchored)
DATE in right month?
2
M-0104
02/01/2026
01/02/2026
02/02/2026
02/02/2026
02/03/2026
02/03/2026
TRUE
3
M-0117
15/01/2026
14/02/2026
15/02/2026
15/02/2026
15/03/2026
15/03/2026
TRUE
4
M-0131
22/01/2026
21/02/2026
22/02/2026
22/02/2026
22/03/2026
22/03/2026
TRUE
5
M-0152
28/01/2026
27/02/2026
28/02/2026
28/02/2026
28/03/2026
28/03/2026
TRUE
6
M-0168
29/01/2026
28/02/2026
01/03/2026
28/02/2026
28/03/2026
29/03/2026
FALSE
7
M-0171
30/01/2026
01/03/2026
02/03/2026
28/02/2026
28/03/2026
30/03/2026
FALSE
8
M-0186
31/01/2026
02/03/2026
03/03/2026
28/02/2026
28/03/2026
31/03/2026
FALSE

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

For a member who joined on 15 January it returns 15 February, as it should. For the 214 who joined on 29, 30 or 31 January it returned 1, 2 and 3 March. No February date meant no February charge, and £8,988 never arrived.

In March the same members met two charges: the one that had slid out of February, and their real March charge on the 29th, 30th or 31st. That was £84 each, and 214 complaints.

Scenario: Enter the seven sample join dates in B2:B8, from 02/01/2026 down to 31/01/2026. Enter =DATE(YEAR(B2),MONTH(B2)+1,DAY(B2)) in D2, fill it to D8, and check that D8 reads 03/03/2026.

Try it in the grid

02DATE Rolls a Missing Day Into the Next Month

DATE never rejects a day number that is too big. It counts the extra days on into the next month, without an error or a warning.

February 2026 has 28 days. Asking for day 31 of February is asking for three days past its end, so the answer is 3 March.

FormulaResult
=DATE(2026,2,28)28/02/2026
=DATE(2026,2,29)01/03/2026
=DATE(2026,2,31)03/03/2026
=DATE(2026,13,15)15/01/2027

The last row is the part that works. Month 13 rolls into January of the next year correctly, so the formula survives December. It only breaks when the day does not exist in the target month.

That is why the sheet passed every test the team ran. A 15th-of-the-month joiner never shows the fault; only the 29th, 30th and 31st do, and only in a short month.

Scenario: Enter =DATE(2026,2,31) in a spare cell and check that it reads 03/03/2026. Then change the 31 to 28 and watch it return 28/02/2026.

Try it in the grid

03Adding 30 Days Drifts Every Month

The first suggested fix was to add 30 days. A month is not 30 days, and the error builds with every charge. For a 31 January joiner, =B2+30 returns 2 March and February still gets nothing.

Fill the pattern down a year and it gets worse. Charges for the 31 January joiner, at =$B2+30*n:

nDateMonth
102/03/2026March
201/04/2026April
301/05/2026May
431/05/2026May again
1226/01/2027January

May gets two charges and February none. For the 15 January joiner the date slips a day or so every couple of months: after twelve charges it is 10 January, five days short of the anniversary.

Adding days is right when the rule is in days. A 30-day free trial ends at =B2+30, whatever the month. A monthly charge is a rule in months, so it needs a function that counts months.

Scenario: Enter =$B$8+30*{1;2;3;4} in a cell on Microsoft 365, or =$B$8+30, +60, +90 and +120 in four cells. Check that two of the dates fall in May.

Try it in the grid

04EDATE Stops at the End of a Short Month

EDATE adds whole months to a date. When the day does not exist in the target month, it returns that month's last day instead of running on into the next one:

=EDATE(B2,1)

For 29, 30 and 31 January it returns 28 February. For 15 January it returns 15 February, exactly as DATE did. Only the rows that were wrong change.

JoinedDATE, +1 monthEDATE, +1 month
15/01/202615/02/202615/02/2026
29/01/202601/03/202628/02/2026
31/01/202603/03/202628/02/2026

It helps to see what EDATE is doing. It is the smaller of the overflowing DATE and the target month's last day:

=MIN(DATE(YEAR(B2),MONTH(B2)+1,DAY(B2)),EOMONTH(B2,1))

Format the cell as a date. EDATE returns a serial number, so a new cell may show 46081 instead of 28/02/2026. Apply a date format, or press Ctrl+Shift+3.

Scenario: Enter =EDATE(B2,1) in E2 and fill it to E8. Check that E6, E7 and E8 all read 28/02/2026 and that E3 reads 15/02/2026.

Try it in the grid

05Chaining EDATE Loses the 31st for Good

The second fix went in with EDATE, but it built each month from the one before. February's 28th became the anchor, and every later charge stayed on the 28th:

=EDATE(E2,1)     chained: 28/02 becomes 28/03, 28/04...
=EDATE($B2,2)    anchored: 31/01 becomes 31/03

For the 85 members who joined on 31 January, March's charge came on the 28th, three days early. Many had picked the 31st because it is payday, so the early debits arrived before the salary did.

The fix is to count every charge from the join date. Put the charge number in row 1, from 1 in J1 across to 12 in U1, and use one formula for the whole schedule:

=EDATE($B2,J$1)

Now the short month only affects itself. The 31 January joiner is charged on 28 February, 31 March, 30 April and 31 May.

Scenario: Enter =EDATE(E8,1) in F8 and =EDATE($B8,2) in G8. Check that F8 reads 28/03/2026 and G8 reads 31/03/2026.

Try it in the grid

06Prove Each Charge Lands in Its Own Month

A monthly schedule has one rule you can test: the first charge belongs in the month after joining. EOMONTH gives the last day of any month, so compare the charge's month-end with the expected one:

=EOMONTH(D2,0)=EOMONTH(B2,1)

In column H it reads TRUE for the members who joined up to the 28th. For the 29th, 30th and 31st it reads FALSE, because their charge sits in March's month-end, not February's.

Count the failures so the check cannot hide in a long column:

=COUNTIF(H2:H8,FALSE)

On the sample it returns 3. On Ashcombe's full list of January joiners it returned 214, one for every member the February run missed. Point the same check at the EDATE column and it reads 0.

Scenario: Enter =EOMONTH(D2,0)=EOMONTH(B2,1) in H2 and fill it to H8. Then enter =COUNTIF(H2:H8,FALSE) in H10 and check that it reads 3.

Try it in the grid

07Month-End Billing and Leap Years

EDATE keeps the join day whenever it exists. That is a policy, and it is not the only one. Some clubs bill everyone who joined at a month-end on the last day of every month.

JoinedRuleMarch chargeApril charge
28/02/2026=EDATE(B2,n)28/03/202628/04/2026
28/02/2026=EOMONTH(B2,n)31/03/202630/04/2026

For a month-end rule, use EOMONTH with the same charge number. Decide which rule the contract says before you pick the function, not after.

Leap years follow the same logic. =EDATE(DATE(2028,1,31),1) returns 29/02/2028, and a member who joins on 29 February 2028 is charged a year later on 28/02/2029.

A fixed term ends the day before the anniversary. A 12-month contract from 15 January 2026 covers up to =EDATE(B2,12)-1, which is 14/01/2027.

Scenario: Enter 28/02/2026 in B12. Enter =EDATE(B12,1) in C12 and =EOMONTH(B12,1) in D12, and check that they read 28/03/2026 and 31/03/2026.

Try it in the grid

08Five Traps

  1. Adding a month with DATE. Day 29, 30 or 31 rolls into the next month whenever the target month is short. Use EDATE.
  2. Adding 30 days for a month. The date drifts every month, and some months get two charges or none.
  3. Chaining EDATE off the last result. One short month drags every later date to the 28th. Count from the original date.
  4. Text that looks like a date. EDATE returns #VALUE! for "31/01/2026" stored as text. Convert the column before you add months.
  5. Fractions of a month. EDATE ignores the decimals, so =EDATE(B2,1.5) is one month, not six weeks.

09Four Exercises

  1. Reproduce it. Enter the seven members and confirm DATE puts three February charges in March.
  2. Fix it. Replace the formula with =EDATE(B2,1) and confirm all seven charges fall in February.
  3. Anchor it. Build a 12-charge schedule with =EDATE($B2,J$1) and confirm the 31 January joiner is charged on 31 May.
  4. Check it. Add the month-end test from section 6 and confirm COUNTIF reads 0 on the EDATE column.

What to Take Away

Every date on Ashcombe's sheet was a real date, and DATE did exactly what it is built to do. It counted days past the end of February and landed in March.

Add months with EDATE, always counting from the start date, and test that each charge lands in its own month. Short months only bite the 29th, 30th and 31st, which is why a test with the 15th never finds them.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge