Marlowe Freight recharges carriage to one retail client every month, and for a year the schedule footed. The depot carried £1,196,344.50. The client was invoiced £1,159,139.90. Nothing on the sheet was red.
The difference is £37,204.60, and all of it is one day a month. Ten of the twelve months were short by exactly what Kirkburn carried on the last day. The other two were right to the penny.
The dates in the export were not dates. Every stamp carried a time of day, the column was formatted dd/mm/yyyy, and not one cell on the sheet showed a clock.
| The year's recharge | Figure |
|---|---|
| Carried at Kirkburn | £1,196,344.50 |
| Recharged to the client | £1,159,139.90 |
| Never invoiced | £37,204.60 |
| Months short | 10 of 12 |
| Still inside the 90 days | £11,010.90 |
Scope.
SUMIFS,COUNTIFS,EOMONTH,MODandINT, plus PivotTable grouping, AutoFilter and Power Query, in Excel for Microsoft 365, 2021, 2019 and Excel for Mac.A date in Excel is a number of days and a time is the fraction after the point. Everything below follows from that one fact, and none of it depends on which date system the workbook uses.
Throughout, column B holds the delivery stamp, column F the net carriage, and H2 the first day of the month being recharged.
01Ten Months Short, Two Months Exact
Marlowe Freight runs sixty vehicles out of Kirkburn and recharges carriage to its largest retail client monthly. The schedule is one row per month, and each row is a SUMIFS over the handheld scanner export.
The export is one line per consignment: a reference, a delivery stamp, a weight and a net charge. Just under four thousand rows a month, pasted in under last month's.
The recharge formula fences the month between its first day and its last: =SUMIFS(F:F,B:B,">="&H2,B:B,"<="&EOMONTH(H2,0)). It reads exactly like what it is meant to do, and it is wrong by one day every month.
Ten of the twelve months came up short. August and November did not, because 31 August and 30 November 2025 both fell on a Sunday, Kirkburn does not collect on a Sunday, and a month with nothing on its last day has nothing to lose.
The August reconciliation footed to the penny. It was run in September against the depot's own figure, it matched exactly, and nobody opened the other eleven months for another six months.
A Year of Monthly Recharges, Ten of Them a Day Short
Marlowe Freight's carriage recharge schedule for one retail client, April 2025 to March 2026, in A1:F15. Column C is what the depot's own scanner export says it carried in the month, and column D is what the recharge formula returned and the client was invoiced. Column E is the difference, which in every case is exactly what the depot carried on the last day of the month. The two rows where C and D agree are the two months whose last day fell on a Sunday; the two rows at the bottom are the year and the part of it still inside the client's ninety-day re-billing window.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02A Date Cell Can Hold a Time You Cannot See
Excel stores a date as a whole number of days and a time as the fraction after the point. 31/03/2026 is 46112, and 31/03/2026 16:42 is 46112.695833.
A cell formatted dd/mm/yyyy displays both of those as 31/03/2026. The number format rounds what you see; it never rounds what the cell holds, and every criterion you write is compared against what the cell holds.
So a column that looks like a year of clean dates can hold four thousand distinct numbers with barely a whole one among them.
| What you ask | On a clean date | On a 16:42 stamp |
|---|---|---|
| What the cell shows | 31/03/2026 | 31/03/2026 |
=B2=DATE(2026,3,31) | TRUE | FALSE |
=MOD(B2,1) | 0 | 0.695833 |
=TEXT(B2,"hh:mm") | 00:00 | 16:42 |
Scenario: Put =TEXT(B2,"dd/mm/yyyy hh:mm") beside the first stamp in your own export and fill it down ten rows. Anything other than 00:00 at the end of the line is a row your month-end total is about to drop.
03One Cell Counts Every Stamp With a Time
One formula says whether you have this problem before you go looking for it. =SUMPRODUCT(--(MOD(B2:B4000,1)>0)) counts the cells in the date column whose fraction is not zero.
On Kirkburn's export it returns 3,964, which is every row in the file. On a hand-keyed sheet it returns 0, and none of the fences below ever matter.
An answer between the two is the dangerous one. It means two feeds are landing in one column, and whichever of them writes midnight is the only one your criteria can see.
| Check | Formula | Kirkburn |
|---|---|---|
| Rows with a time | =SUMPRODUCT(--(MOD(B2:B4000,1)>0)) | 3,964 |
| Earliest time of day | =TEXT(MIN(MOD(B2:B4000,1)),"hh:mm") | 05:48 |
| Latest time of day | =TEXT(MAX(MOD(B2:B4000,1)),"hh:mm") | 21:17 |
| Cells holding text | =COUNTA(B2:B4000)-COUNT(B2:B4000) | 0 |
The two middle rows are array formulas. On Excel 2019 and earlier they need Ctrl+Shift+Enter; on Microsoft 365 and Excel 2021 they are ordinary formulas.
Scenario: Put =SUMPRODUCT(--(MOD(B2:B4000,1)>0)) in a cell above your own date column and leave it there. Any answer above 0 means every date criterion on the sheet has to be a fence rather than an equals.
04Why a <= End Date Drops the Last Day
EOMONTH(H2,0) returns the last day of the month at midnight. For March 2026 that is 46112 exactly, not 46112.999, so "<="&EOMONTH(H2,0) means "at or before midnight on the 31st".
Midnight on the 31st is the instant the day begins. A consignment scanned at 06:12 that morning is already past the fence, and so is every consignment delivered after it.
Kirkburn carried 148 consignments on 31 March 2026, worth £4,650.10, stamped between 06:12 and 19:48. The recharge took none of them.
| March 2026 | Figure |
|---|---|
| Carried in the month | £112,045.60 |
| Recharged to the client | £107,395.50 |
| Dropped on the 31st | £4,650.10 |
| Consignments dropped | 148 |
The same fence is correct on a hand-keyed sheet and wrong on an export. That is why it survives review: the person reading it has watched it work for years on a column of clean dates.
05The Fence That Works on Any Date Column
Fence a period with a >= on its first instant and a < on the first instant of the next period. The upper bound then sits beyond every time of day the last date could hold, without you having to name one.
=SUMIFS(Net,Stamp,">="&H2,Stamp,"<"&EOMONTH(H2,0)+1)
EOMONTH(H2,0)+1 is the first day of the following month at midnight. A stamp of 31/03/2026 19:48 is below it; a stamp of 01/04/2026 00:00 is not. Nothing is counted twice and nothing is missed.
The same shape does a day, a week or a quarter:
=SUMIFS(Net,Stamp,">="&H2,Stamp,"<"&H2+1)
=SUMIFS(Net,Stamp,">="&H2,Stamp,"<"&H2+7)
=SUMIFS(Net,Stamp,">="&H2,Stamp,"<"&EDATE(H2,3))
A half-open fence is right on both kinds of column. On clean dates it still includes the last day, because nothing sits between that midnight and the next one. On stamps it includes the whole of it.
Scenario: Take your own month-end SUMIFS, replace "<="&EOMONTH(H2,0) with "<"&EOMONTH(H2,0)+1, and put the two totals side by side. The difference is a day of trading nobody has ever invoiced.
06An Exact Date Match Returns Nothing at All
The loud version of this fault is an exact match. =SUMIFS(Net,Stamp,H2) with a clean date in H2 returns 0.00 against a timestamped column, because not one stamp in it equals midnight.
Nobody ships that. A daily report of zeros is noticed the first morning it runs and corrected before lunch, and the person who corrects it learns something.
The fence is the dangerous one. It returns a figure of the right size, in the right currency, about three per cent light, and it does it every month for a year.
| Criterion | On a stamped column | Noticed |
|---|---|---|
=SUMIFS(Net,Stamp,H2) | 0.00 | The first morning |
">="&H2 with "<="&EOMONTH(H2,0) | 3% light | Never |
">="&H2 with "<"&EOMONTH(H2,0)+1 | Correct | — |
COUNTIFS behaves exactly the same way. =COUNTIFS(Stamp,H2) returns 0 on Kirkburn's March export, and =COUNTIFS(Stamp,">="&H2,Stamp,"<"&EOMONTH(H2,0)+1) returns 3,964.
Scenario: Put =SUMIFS(Net,Stamp,H2) beside your fenced total for the same month. A zero tells you the column is stamped, and it tells you in one cell without a helper column anywhere.
07INT Strips the Time, ROUND Moves the Day
When the time genuinely has to go, INT is the function that removes it. =INT(B2) throws the fraction away and leaves the date, and a helper column of it can be matched with an ordinary equals.
=FLOOR(B2,1) does the same thing, and so does =TRUNC(B2). All three keep the stamp on the day it landed on.
ROUND does not. =ROUND(B2,0) goes to the nearest whole day, so every stamp after 12:00 moves to the following morning — most of Kirkburn's afternoon, and all 148 consignments of 31 March.
| On 31/03/2026 16:42 | Result |
|---|---|
=INT(B2) | 31/03/2026 |
=FLOOR(B2,1) | 31/03/2026 |
=TRUNC(B2) | 31/03/2026 |
=ROUND(B2,0) | 01/04/2026 |
Keep the helper column as a formula rather than pasting it over itself as values. The day the export changes shape, a formula column follows it and a pasted one carries last month's dates into this month's report.
Scenario: Put =INT(B2) and =ROUND(B2,0) beside a stamp of 16:42 and format both as dd/mm/yyyy. One of them is the day the van delivered; the other is the next morning.
08Fix It Once in Power Query
A helper column treats the symptom on one sheet. If the export is refreshed rather than pasted, the place to take the time off is the query, and then every formula downstream sees whole dates.
In the editor, select the column and use Transform ▸ Date ▸ Date Only. The step is recorded once and applies to every refresh after it, however many rows arrive.
Changing the column's type to Date does the same job. The type icon in the column header is the quickest way to read which one you have: a calendar is a date, and a calendar with a clock is a date/time.
Do this at the right end. If the time of day is used anywhere — a delivery window, a service-level clock, an out-of-hours surcharge — keep the stamp and add a date column beside it rather than truncating in place.
A stamp recorded in UTC is a second question. Truncating it dates a 23:40 local delivery to the previous day in winter, and the fix is to convert to local time first and strip the time afterwards.
Scenario: Open your export in Power Query, select the date column, and read the icon in its header. If it shows a clock, add Date Only as a step and refresh the sheet that depends on it.
Try it in the grid09Stamps That Arrived as Text
A date column can fail a criterion for a second reason that looks identical from the outside. If the stamps came in as text — "31/03/2026 16:42" as a string — nothing matches, because text is never compared against a number.
=COUNTA(B2:B4000)-COUNT(B2:B4000) is the count that separates the two cases. It returns 0 when the column holds real dates and the number of text cells when it does not.
Text cells sit on the left of their column unless somebody has aligned them, and SUMIFS returns 0.00 for the month rather than a figure that is slightly light.
=DATEVALUE(LEFT(B2,10)) converts one of them, taking the date part and leaving the time behind. For a whole column at once, Data ▸ Text to Columns ▸ Finish re-reads the column using your regional settings, which is faster and assumes your machine reads the day and the month in the order the file wrote them.
Scenario: Put =COUNTA(B2:B4000)-COUNT(B2:B4000) above your date column. Anything but 0 means some of the rows are text, and those rows are invisible to every date criterion on the sheet.
10Pivots, Filters and Lookups Fail Alike
A PivotTable on an ungrouped stamp column gives one row per distinct second, so March 2026 reports 3,964 dates rather than 31. Right-click the field, choose Group, and pick Months: the grouping buckets on the date part and the report is correct.
AutoFilter is the reason people conclude the data is fine. Its tick list builds a Year ▸ Month ▸ Day tree and files every stamp under its day, so the 31st is plainly there in the filter while the formula beside it says otherwise.
A lookup fails like an exact-match SUMIFS. =XLOOKUP(H2,Stamp,Net) returns #N/A against a stamped column, and =XLOOKUP(H2,INT(Stamp),Net) finds the first delivery of that day.
| Where it shows up | What goes wrong | The fix |
|---|---|---|
| PivotTable rows | One row per stamp | Group ▸ Months |
XLOOKUP on a date | #N/A | Match on INT |
| Conditional format | Rule never fires | =INT($B2)=TODAY() |
| Data validation | Every date rejected | Fence the rule |
Scenario: Add a conditional formatting rule of =INT($B2)=TODAY() to your export and check that today's deliveries light up. The version without INT is the one that highlights nothing all day.
11TODAY, NOW and the Criteria Cell Itself
TODAY() returns a whole day with no time on it, which is why it is safe on either side of a fence. =SUMIFS(Net,Stamp,">="&TODAY(),Stamp,"<"&TODAY()+1) is everything delivered today, whatever time it was scanned.
NOW() carries the time, so ">="&NOW() means "from this second onwards" and returns the afternoon's deliveries at two o'clock and almost nothing at five.
The criteria cell is worth the same check as the data. If H2 was pasted out of the export rather than typed, it may carry 08:00 of its own, and a fence built on it starts eight hours late.
Scenario: Put =MOD(H2,1) beside the cell your report's dates come from. If it is not 0, wrap the criteria cell in INT before anything else on the sheet reads it.
12Eight Traps
- Formatting the column as a date. It changes what you see and not one comparison underneath it.
"<="on the last day. It stops at midnight, and midnight is where the day starts rather than where it ends.ROUNDinstead ofINT. Everything after noon moves to the next day, and the total is wrong in both directions at once.- A criteria cell carrying its own time. The fence is right and the bound is eight hours late.
NOW()whereTODAY()was meant. Today's total shrinks every time the sheet recalculates.- Pasting the helper column as values. It stops following the export the first time the export changes shape.
- Trusting the filter. The tick list groups stamps by day;
SUMIFScompares the number, and the two disagree. - Spot-checking one month. August footed because the 31st was a Sunday, and a sample of one proved the other eleven nothing.
13Five Exercises
- Reproduce it. Put twenty stamps across two months into a column, total one month with
"<="&EOMONTH(H2,0), and confirm the last day is missing. - Count the times.
=SUMPRODUCT(--(MOD(B2:B21,1)>0))should report every stamped row before you change anything. - Fence it. Swap the upper bound for
"<"&EOMONTH(H2,0)+1and watch the total move by exactly one day of trading. - Break it with ROUND. Add
=ROUND(B2,0)as a helper column, total on that instead, and find the afternoon in the following month. - Fix the source. Load the same rows through Power Query, add Date Only, and check that the exact-match
SUMIFSnow returns the day.
What to Take Away
Nothing on this schedule was broken. Twelve months, twelve plausible figures, a formula that reads like the sentence somebody would say out loud, and a reconciliation that passed.
What the sheet never showed was the time of day sitting behind every date in the export. A number format hid it, the filter grouped it away, and only the arithmetic underneath the criteria ever saw it.
A date criterion is safe when it is a fence: >= on the first instant, < on the first instant of the next period. That form is right on timestamps and still right on clean dates, so it is the one to write by default.
One cell says whether it matters. =SUMPRODUCT(--(MOD(B2:B4000,1)>0)) costs nothing to leave on the sheet, against £26,193.70 that went out of the ninety-day window while the schedule footed.