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 joiners | Members | February charge dated |
|---|---|---|
| Joined 29 Jan | 61 | 01/03/2026 |
| Joined 30 Jan | 68 | 02/03/2026 |
| Joined 31 Jan | 85 | 03/03/2026 |
| Total | 214 | £8,988 not collected |
Scope.
EDATE,EOMONTH,DATE,YEAR,MONTH,DAY,MINandCOUNTIFwork 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.
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.
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.
| Formula | Result |
|---|---|
=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.
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:
| n | Date | Month |
|---|---|---|
| 1 | 02/03/2026 | March |
| 2 | 01/04/2026 | April |
| 3 | 01/05/2026 | May |
| 4 | 31/05/2026 | May again |
| 12 | 26/01/2027 | January |
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.
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.
| Joined | DATE, +1 month | EDATE, +1 month |
|---|---|---|
| 15/01/2026 | 15/02/2026 | 15/02/2026 |
| 29/01/2026 | 01/03/2026 | 28/02/2026 |
| 31/01/2026 | 03/03/2026 | 28/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.
EDATEreturns 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.
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.
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.
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.
| Joined | Rule | March charge | April charge |
|---|---|---|---|
| 28/02/2026 | =EDATE(B2,n) | 28/03/2026 | 28/04/2026 |
| 28/02/2026 | =EOMONTH(B2,n) | 31/03/2026 | 30/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.
08Five Traps
- Adding a month with
DATE. Day 29, 30 or 31 rolls into the next month whenever the target month is short. UseEDATE. - Adding 30 days for a month. The date drifts every month, and some months get two charges or none.
- Chaining
EDATEoff the last result. One short month drags every later date to the 28th. Count from the original date. - Text that looks like a date.
EDATEreturns #VALUE! for "31/01/2026" stored as text. Convert the column before you add months. - Fractions of a month.
EDATEignores the decimals, so=EDATE(B2,1.5)is one month, not six weeks.
09Four Exercises
- Reproduce it. Enter the seven members and confirm
DATEputs three February charges in March. - Fix it. Replace the formula with
=EDATE(B2,1)and confirm all seven charges fall in February. - Anchor it. Build a 12-charge schedule with
=EDATE($B2,J$1)and confirm the 31 January joiner is charged on 31 May. - Check it. Add the month-end test from section 6 and confirm
COUNTIFreads 0 on theEDATEcolumn.
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.