01 / 10The Rule Worked for Six Years

Date FunctionsMODFormula Auditing

Fortnightly Pay Dates in Excel: Use MOD, Not Week Numbers

The Cash Forecast Shows No Payroll on 7 January. £38,450 Goes Out Anyway, Because 2026 Has a Week 53

20 Nov 20268 min read

UsesMODEVEN, ODDIFSUMPRODUCT

Kestrel Joinery's 13-week cash forecast shows a closing balance of £31,200 for the week of 4 January 2027. The £38,450 payroll that leaves on Thursday 7 January is not in it, so the account will be £7,250 overdrawn.

Week of 4 January 2027The forecastThe bank
Payroll out£0£38,450
Closing balance£31,200-£7,250

Scope. ISOWEEKNUM, ISEVEN, MOD, IF, DATE and SUMPRODUCT work in Excel 2013 and later. SEQUENCE needs Excel 2021 or Microsoft 365.

The same fault hits anything that repeats every other week: on-call rotas, fortnightly collections, alternate-week shifts. Payroll is simply where it costs money first.

01The Rule Worked for Six Years

Kestrel pays every other Thursday. The forecast picks those weeks with one test: is the ISO week number even? It has been right every week since it was written in January 2021.

The forecast runs one row per week, with the Monday of each week in column A:

H1:  38450                          fortnightly payroll
A2:  07/12/2026                     week commencing
B2:  =ISOWEEKNUM(A2)                → 50
C2:  =IF(ISEVEN(B2),$H$1,0)         → 38,450

Week 50 is even, so it is a pay week. Week 51 is odd and shows nothing. Week 52, Christmas Eve, pays again.

The rule quietly assumes that even and odd weeks alternate forever. That holds only while every year has an even number of weeks.

Scenario: Put 07/12/2026 in A2, =A2+7 in A3, and fill down to A14. Enter =ISOWEEKNUM(A2) in B2 and fill it down. Check that B5 shows 53 and B6 shows 1.

Try it in the grid

02Week 53 Makes Two Odd Weeks in a Row

ISO years have 52 or 53 weeks, and 2026 has 53. Week 53 is odd, and so is week 1 of 2027 after it, so the forecast skips two weeks running.

Kestrel Joinery's Weekly Cash Forecast Around Week 53

Seven weeks of Kestrel Joinery’s 13-week cash forecast, from 7 December 2026 to 18 January 2027. A is the Monday of each week, B the ISO week, =ISOWEEKNUM(A2), C the payroll the forecast shows, =IF(ISEVEN(B2),$H$1,0), and D the payroll that actually leaves, every other Thursday from 8 January 2026.

ABCDE
1
Week commencing
ISO week
Forecast payroll
Actual payroll
What happened
2
07/12/2026
50
38450
38450
Payday on Thursday 10 December. The forecast and the bank agree.
3
14/12/2026
51
0
0
Off week. Right.
4
21/12/2026
52
38450
38450
Christmas Eve payroll. Right.
5
28/12/2026
53
0
0
Week 53 is odd, so no payroll. Right: it is an off week.
6
04/01/2027
1
0
38450
Week 1 is odd too. The payroll leaves on 7 January and the forecast misses it.
7
11/01/2027
2
38450
0
The forecast books the payroll here, a week after it has gone.
8
18/01/2027
3
0
38450
Missed again. Every 2027 payday is now a week late.

fxCells with formulas are highlighted in green

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

Week 53 is genuinely an off week: Christmas Eve was the last payday. The damage is week 1, where 14 days have passed since Christmas Eve and the payroll is due.

Week commencingISO weekForecastActual
21/12/202652£38,450£38,450
28/12/202653£0£0
04/01/20271£0£38,450
11/01/20272£38,450£0

The payroll does not vanish from the forecast. It moves to week 2, a week after the money has already gone.

Scenario: Enter =IF(ISEVEN(B2),38450,0) in C2 and fill it down to C14. Check that C5 and C6 both show 0, the only two empty weeks in a row in the whole forecast.

Try it in the grid

03Every 2027 Payday Now Lands a Week Late

The slip is not a one-off. From week 1 onwards, every real payday falls in an odd week, and the forecast books each one seven days late.

Across the 13 weeks from 7 December, the forecast and the bank disagree in 9 weeks out of 13:

13 weeks to 1 March 2027ForecastActual
Payroll runs67
Payroll total£230,700£269,150
Weeks that disagree9

In every real pay week of 2027, the forecast balance is £38,450 too high. The following week it catches up, which is why the closing totals look plausible.

2027 to 2031 all have 52 weeks, so nothing puts it back. The forecast stays a week out until 2033, after the next 53-week year flips it again.

Scenario: In D2, type the real payroll for each week: 38450 in the weeks of 7 December, 21 December, 4 January, 18 January, 1 February, 15 February and 1 March. Enter =SUMPRODUCT(--(C2:C14<>D2:D14)) and check that it returns 9.

Try it in the grid

04Count 14 Days From a Known Payday With MOD

A fortnight is 14 days, not two week numbers. Count the days since a payday you know, and pay when that count divides by 14:

H2:  08/01/2026                     first payday of 2026
C2:  =IF(MOD(A2+3-$H$2,14)=0,$H$1,0)

A2+3 is the Thursday of each week. For the week of 4 January 2027 that is 7 January, 364 days after 8 January 2026, and 364 divides by 14, so the payroll is in.

Nothing here depends on how many weeks a year has. The count of days never resets, so the rule is right in 2027, in 2032 and every year after.

If you prefer to keep ISEVEN, feed it a week count from a fixed Monday instead of a week number. =ISEVEN((A2-DATE(2026,1,5))/7) is TRUE in exactly the same weeks.

Scenario: Put 08/01/2026 in H2 and replace C2 with =IF(MOD(A2+3-$H$2,14)=0,$H$1,0). Fill it down to C14 and check that C6, the week of 4 January, now shows 38,450.

Try it in the grid

05Check for Two Empty Weeks in a Row

On a fortnightly schedule, pay weeks and off weeks must strictly alternate. Two empty weeks side by side, or two pay weeks, means the pattern has slipped:

=SUMPRODUCT((C2:C13=0)*(C3:C14=0))     two off weeks in a row
=SUMPRODUCT((C2:C13>0)*(C3:C14>0))     two pay weeks in a row

Each formula compares every week with the one after it, by offsetting the two ranges by a row.

CheckISEVEN(B2)MOD
Two off weeks in a row10
Two pay weeks in a row00

Put both checks above the forecast with a target of zero. The week-53 slip shows up the day the rolling window reaches it, weeks before the money moves.

Scenario: Enter =SUMPRODUCT((C2:C13=0)*(C3:C14=0)) next to the forecast. Check that it returns 1 with the ISEVEN rule and 0 with the MOD rule.

Try it in the grid

06WEEKNUM Only Moves the Break

Switching to WEEKNUM looks like a fix, because it happens to put 2027 right. It counts weeks from 1 January with Sunday starts, so its numbering breaks in different years.

=IF(ISEVEN(WEEKNUM(A2+3)),$H$1,0) marks the right Thursdays in 2027 and 2028. On Thursday 4 January 2029 it goes wrong again, and stays wrong for the next five years.

RuleRight inFirst wrong payday
ISEVEN(ISOWEEKNUM)2021 to 20267 January 2027
ISEVEN(WEEKNUM)2027 and 20284 January 2029
MOD from a paydayEvery yearNone

Any rule built on a week number inherits the calendar's leftover days. Only a count from a fixed date steps in exact fortnights.

Scenario: Enter =WEEKNUM(DATE(2029,1,4)) and =WEEKNUM(DATE(2028,12,21)). Check that they return 1 and 52: two pay weeks that ISEVEN(WEEKNUM) would both reject.

Try it in the grid

07Know Which Years Have 53 Weeks

28 December always falls in the last ISO week of its year. So one formula tells you whether a year has a week 53:

=ISOWEEKNUM(DATE(2026,12,28))=53     → TRUE
YearISO weeks
202053
202653
203253
203753

Every other year from 2020 to 2037 has 52. Any schedule built on week numbers in a 52-week year will survive until the next one in this list, which is why it gets past testing.

To list the paydays themselves, step from a known one: =DATE(2027,1,7)+14*SEQUENCE(26,1,0) returns all 26 Thursdays from 7 January to 23 December 2027.

Scenario: Put the years 2026 to 2032 in a column and enter =ISOWEEKNUM(DATE(A2,12,28))=53 beside them. Check that only 2026 and 2032 return TRUE.

Try it in the grid

08Five Traps

  1. Testing a week number for odd or even. It works until a 53-week year, then every payday after it is a week out.
  2. Fixing it by flipping the test. Changing ISEVEN to ISODD in January puts 2027 right and 2033 wrong.
  3. Swapping to WEEKNUM. It breaks in other years instead, starting 4 January 2029.
  4. Budgeting 26 runs every year. Fortnightly pay from 8 January 2026 has 27 paydays in 2032.
  5. Trusting the closing total. A payday a week late leaves the 13-week total right in most windows, so only a weekly check catches it.

09Four Exercises

  1. Reproduce it. Build the ISEVEN(B2) forecast from 7 December 2026 and confirm C6 shows 0.
  2. Fix it. Replace column C with the MOD rule and confirm the 13-week total is 269,150.
  3. Check it. Confirm both SUMPRODUCT checks return 0 on the fixed forecast.
  4. Stretch it. Fill the forecast down to row 320, past the end of 2032, extend both checks to that row, and confirm they still return 0.

What to Take Away

The week-number rule never made a mistake until the calendar did something it had not done for six years. Every 2027 payday is a week late in the forecast, and the first one overdraws the account.

Step fortnights from a known date with =IF(MOD(A2+3-$H$2,14)=0,$H$1,0), and check that no two weeks in a row are both empty.

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

Price a Balloon Finance Deal with PMT's Fourth Argument

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

Solve today’s challenge