Hartwell Print closed its Bradwell finishing room on Monday 19 October 2026. Eight people left that day, each owed three weeks' pay for every complete year of service. The sheet that worked out the payments overpaid four of them.
The sheet counted service with one formula, =INT(($H$1-B2)/365): the days between start date and leaving date, divided by 365, cut to a whole number. It credits a year a day early for every leap day in between.
| Bradwell leavers, 19 October 2026 | Figure |
|---|---|
| Leavers | 8 |
| Paid by the sheet | £204,802.50 |
| Owed under the scheme | £196,646.40 |
| Overpaid, to four leavers | £8,156.10 |
| Short-paid by the 365.25 "fix" | £6,310.80 |
Scope.
DATEDIF,YEARFRAC,EDATE,YEAR,DATEandTODAYbehave the same in every current version of Excel on Windows, Mac and the web, and in Google Sheets.DATEDIFis missing from Excel's function list and autocomplete, but it calculates in all of them.Throughout,
H1holds the leaving date, column B the start date and column C the weekly pay. A complete year means the anniversary has arrived on or before the leaving date.
01Four Leavers Were Paid a Year Too Many
The payments went out with final pay on Friday 23 October. On 26 October the HR system's own service report, run for the pension provider, disagreed with the sheet on four names.
Kerry Marsh started on 20 October 2021 and left on 19 October 2026, one day short of five years. The sheet said five. Three weeks' pay for the fifth year was £1,837.20.
Elin Lindqvist was one day short of ten years, Daniel Osei two days short of fifteen, and Rob Whitlock four days short of twenty. Each was paid for a year that ended after the site closed.
The other four rows were right. Two of them were right only just: Tomi Adeyemi and Ann Pryce left on an anniversary to the day, which is exactly where the fix somebody proposed goes wrong.
Eight Leavers, Four Extra Years and Three Exact Anniversaries
The Bradwell closure's redundancy sheet, in A1:G11, with the leaving date (19/10/2026) in H1. Column D is what the sheet counted with `=INT(($H$1-B2)/365)` and column E the complete years `=DATEDIF(B2,$H$1,"y")` returns. Where they differ, column F is the overpayment: three weeks' pay for the extra year. The four wrong rows are one, one, two and four days short of an anniversary; Adeyemi, Pryce and Faulkner left on theirs exactly, which is where dividing by 365.25 fails. The three rows underneath are the totals.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02Dividing by 365 Counts Leap Days as Service
Subtracting one date from another gives the days between them, because Excel stores a date as a day number. Dividing by 365 then assumes every year is 365 days long. One year in four is not.
Five years from 20 October 2021 is 1,826 days, because 29 February 2024 falls inside them. The sheet reaches 5.000 at 1,825 days, which is the day before the anniversary.
| Years of service | Leap days inside | Days the sheet runs early |
|---|---|---|
| 5 | 1 or 2 | 1 or 2 |
| 10 | 2 or 3 | 2 or 3 |
| 15 | 3 or 4 | 3 or 4 |
| 20 | 5 | 5 |
| 25 | 6 or 7 | 6 or 7 |
The longer the service, the wider the window. A leaver inside it is paid the next year early; everyone further from an anniversary is counted correctly, which is why the sheet passed a spot check.
Scenario: Put 20/10/2021 in B2 and 19/10/2026 in H1, then enter =($H$1-B2)/365 in C2. It returns 5.000 the day before the fifth anniversary. Change H1 to 18/10/2026 and watch it drop below five.
03Dividing by 365.25 Moves the Error Instead
The usual repair is to divide by 365.25, the average length of a year. It undercounts instead, on the anniversary itself, whenever fewer leap days have passed than the average assumes.
Tomi Adeyemi started on 19 October 2021 and left on 19 October 2026: five complete years, 1,826 days. Divided by 365.25 that is 4.9993, and INT makes it four.
| Leaver | Days | ÷ 365 | ÷ 365.25 | Complete years |
|---|---|---|---|---|
| Adeyemi, T. | 1,826 | 5.003 | 4.999 | 5 |
| Marsh, K. | 1,825 | 5.000 | 4.997 | 4 |
| Pryce, A. | 3,652 | 10.005 | 9.999 | 10 |
| Lindqvist, E. | 3,651 | 10.003 | 9.996 | 9 |
| Faulkner, B. | 9,131 | 25.016 | 24.999 | 25 |
On the Bradwell list the 365.25 version cures all four overpayments and short-pays the three anniversary leavers by £6,310.80. Neither divisor counts complete years. Both approximate them, and the approximation fails exactly where the money changes.
Scenario: Enter 19/10/2021 in B2 and 19/10/2026 in H1, then compare =INT(($H$1-B2)/365.25) with =DATEDIF(B2,$H$1,"y"). The first says four on the fifth anniversary; the second says five.
04DATEDIF Counts Complete Years Like a Calendar
DATEDIF(start_date, end_date, "y") returns the whole years between two dates. It divides nothing. It checks whether this year's anniversary has arrived by the end date, and counts the years up to the last one that has.
=DATEDIF(B2,$H$1,"y") complete years of service
=3*C2*DATEDIF(B2,$H$1,"y") three weeks' pay per complete year
For Kerry Marsh it returns 4 on 19 October 2026 and 5 on the 20th. For Tomi Adeyemi it returns 5 on the 19th itself. Leap days make no difference, because nothing is divided by the length of a year.
Three things to know before filling it down:
- The start date comes first. If it is later than the end date,
DATEDIFreturns#NUM!. A blank start date counts as 1900, so guard it:=IF(B2="","",DATEDIF(B2,$H$1,"y")). - It is not in the function list. Excel keeps it for workbooks that came from Lotus 1-2-3 and never added it to autocomplete. Type it in full and it calculates.
- Caps go outside it. A scheme that counts at most 20 years is
=MIN(DATEDIF(B2,$H$1,"y"),20).
Scenario: Fill =DATEDIF(B2,$H$1,"y") down beside a list of start dates, then set H1 to the day before one person's anniversary and then to the day itself. The count should change on the anniversary and on no other day.
05YEARFRAC Is for Fractions, Not Thresholds
YEARFRAC(start_date, end_date, basis) returns years as a decimal, which is what a pro-rata calculation needs. It is the wrong tool for deciding whether a year is complete, because neither of its usual bases counts anniversaries.
The default basis, 0, is a 30/360 day count from bond markets that treats every month as 30 days. Basis 1, actual/actual, divides by the average length of the calendar years the dates span, which is the 365.25 problem with a different average.
Ann Pryce left on the tenth anniversary of starting. =YEARFRAC(B2,$H$1,1) returns 9.998, because the eleven calendar years from 2016 to 2026 average 365.27 days. ROUND turns that into 10.00 and INT turns it into 9.
| Use | Formula |
|---|---|
| Complete years, for a threshold | =DATEDIF(B2,$H$1,"y") |
| Years to two decimals, for pro-rata | =ROUND(YEARFRAC(B2,$H$1,1),2) |
| Avoid for thresholds | =INT(YEARFRAC(B2,$H$1,1)) |
Scenario: With 19/10/2016 in B2 and 19/10/2026 in H1, enter =YEARFRAC(B2,$H$1,1) and show four decimals. It reads 9.9980 on the tenth anniversary. Wrap it in INT and read the nine.
06Age on a Date Is the Same Formula
An age is length of service that starts at birth. =DATEDIF(B2,TODAY(),"y") gives an age today; replace TODAY() with a cell holding a cut-off date when the rule reads "aged 18 on 1 September".
Dividing by 365 gets ages wrong the same way it got Hartwell's leavers wrong. Eighteen years always hold four or five leap days, so a birthday is credited four or five days early. An age check is a threshold, and thresholds are where the error lands.
Keep TODAY() out of anything that must stay fixed. A sheet that works out ages from TODAY() gives a different answer tomorrow. That is right for a live staff list and wrong for a record of what was decided on the day.
Scenario: Put a date of birth in B2 and the date of the 18th birthday in C1, then enter =DATEDIF(B2,C1,"y"). Confirm it shows 18, then change C1 to the day before and confirm it shows 17.
07Leap-Day Birthdays: DATEDIF and EDATE Disagree
Someone born on 29 February has no birthday in three years out of four, and Excel's functions do not agree on when it falls. Decide which day your rule means, then use the function that agrees with it.
DATEDIF treats the anniversary as 1 March in a non-leap year. EDATE, which adds whole months, stops at the end of a short month and lands on 28 February.
| Born 29/02/2008 | On 28/02/2026 | On 01/03/2026 |
|---|---|---|
DATEDIF(B2,date,"y") | 17 | 18 |
EDATE(B2,216)<=date | TRUE | TRUE |
If your rule treats 28 February as the birthday, test the threshold with EDATE instead: =EDATE(B2,12*18)<=C1 reads TRUE from 28 February. Either choice is defensible. A sheet that uses both is not.
08Years and Months, and the "md" Unit to Avoid
DATEDIF takes other units. "m" counts complete months, and "ym" counts the months left over after the complete years, which is how a service statement reads.
=DATEDIF(B2,$H$1,"y")&" years, "&DATEDIF(B2,$H$1,"ym")&" months"
For Kerry Marsh that returns "4 years, 11 months". The unit people reach for next, "md", counts the days left over after complete months. Microsoft's own documentation advises against it, because it can return wrong results.
For the leftover days, subtract instead: =$H$1-EDATE(B2,DATEDIF(B2,$H$1,"m")) gives the days since the last whole month. For Kerry Marsh it returns 29.
09Without DATEDIF: YEAR, DATE and One Comparison
Where DATEDIF is unwelcome, perhaps because a reviewer cannot find it in the function list, complete years come straight from the calendar.
=YEAR($H$1)-YEAR(B2)-(DATE(YEAR($H$1),MONTH(B2),DAY(B2))>$H$1)
The first part counts calendar years between the two dates. The comparison takes one off if this year's anniversary is still to come, because TRUE counts as 1 in arithmetic and FALSE as 0.
For a 29 February start, DATE(2026,2,29) rolls over to 1 March, so this formula agrees with DATEDIF. Both give Kerry Marsh 4 and Tomi Adeyemi 5 on 19 October 2026.
Scenario: Put this formula in column D and =DATEDIF(B2,$H$1,"y") in column E for the same start dates. Add =D2=E2 in column G, fill all three down, and confirm =COUNTIF(G2:G9,FALSE) reads 0.
10Check the Rows Near an Anniversary
The fault only shows on rows close to an anniversary, so look at those rows on purpose. Days to the next anniversary is the column that would have caught Hartwell's sheet before payroll ran.
=EDATE(B2,12*(DATEDIF(B2,$H$1,"y")+1))-$H$1
It returns 1 for Marsh and Lindqvist, 2 for Osei and 4 for Whitlock. Sort by it, and every leaver within a week of a milestone sits at the top for a person to read.
Then compare the two methods outright. =INT(($H$1-B2)/365)<>DATEDIF(B2,$H$1,"y") in a spare column returns TRUE on every row the old formula gets wrong, and =COUNTIF(G2:G9,TRUE) counts them. On the Bradwell list it counts 4.
Totals come last. =SUM(F2:F9) over the corrected pay column gives £196,646.40, and the difference from the £204,802.50 that went out is the amount payroll now has to explain.
Scenario: Add the days-to-next-anniversary column to a staff list, sort it smallest first, and check every row under 7 by hand against the start date. Then move H1 back one day and see which rows change.
Try it in the grid11Six Traps
- Dividing by 365. Leap days count as service, so the sheet credits each anniversary early by one day per leap day.
- Dividing by 365.25. It moves the error onto the anniversary itself, where three of Hartwell's leavers would have lost a year.
INToverYEARFRAC. Basis 1 can return 9.998 on a tenth anniversary, andINTreads that as nine.- Arguments the wrong way round.
DATEDIFwith the later date first returns#NUM!, and a blank start date returns more than a century. - Mixing leap-day rules.
DATEDIFputs a 29 February birthday on 1 March andEDATEputs it on 28 February. Pick one. TODAY()in a record. A decision taken on 19 October should be worked out from 19 October, not from whenever the file is next opened.
12Four Exercises
- Reproduce it. Enter the eight start dates from the table, put
19/10/2026in H1 and fill=INT(($H$1-B2)/365)down. Find the four rows that read a year high. - Try the fix that isn't. Change 365 to 365.25 and note which rows move: four get better and three get worse.
- Count properly. Replace it with
=DATEDIF(B2,$H$1,"y")and check every row against its anniversary by hand. - Build the guard. Add the days-to-next-anniversary column from section 10, sort by it, and set H1 to each of the next five days in turn.
What to Take Away
Hartwell's sheet was wrong on four rows out of eight, and every wrong row was within four days of an anniversary. A spot check of any other row would have passed, which is how it reached payroll.
A year is not a fixed number of days. Count complete years with DATEDIF or the YEAR and DATE comparison, keep YEARFRAC for answers that are fractions, and read every row that sits close to a milestone before the money moves.
The £8,156.10 is now a recovery conversation with four people who have already left. The proposed fix would have added a dispute with three more.