01 / 14Ten Months Short, Two Months Exact

Date FunctionsSUMIFSExcel

Excel Dates With Times: Fix SUMIFS and COUNTIFS Criteria

The Carriage Recharge Ran £37,204.60 Short Across a Year and Two Months Footed to the Penny, Because Every Date in the Scanner Export Carried a Time

23 Oct 202614 min read

UsesSUMIFSCOUNTIFSEOMONTHMOD

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 rechargeFigure
Carried at Kirkburn£1,196,344.50
Recharged to the client£1,159,139.90
Never invoiced£37,204.60
Months short10 of 12
Still inside the 90 days£11,010.90

Scope. SUMIFS, COUNTIFS, EOMONTH, MOD and INT, 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.

ABCDEF
1
Month
Last day
Carried
Recharged
Missed
What happened
2
Apr 2025
Wed 30 Apr
98412.5
94880.3
3532.2
The first month on the new export. One hundred and twelve consignments went out on the Wednesday and none of them reached the invoice, because the fence closed at midnight on the 30th.
3
May 2025
Sat 31 May
101860.4
98945.8
2914.6
A Saturday, which Kirkburn works and the client is billed for. Saturdays are quieter than weekdays, so this is the smallest loss of the ten and the one least likely to be queried.
4
Jun 2025
Mon 30 Jun
96274.8
92155.9
4118.9
A Monday, and Mondays clear the weekend backlog. The last day of a month that ends on a Monday is one of the heaviest days the depot runs, which is why June lost more than May on fewer trading days.
5
Jul 2025
Thu 31 Jul
104593.2
100732.8
3860.4
An ordinary Thursday. Nothing about this row looks wrong on the schedule: the recharge is in the same band as every other month, and the shortfall is inside the month-to-month noise.
6
Aug 2025
Sun 31 Aug
92158.6
92158.6
0
The 31st was a Sunday and Kirkburn does not collect on a Sunday, so the month had nothing on its last day to lose. This is the month the September reconciliation sampled, and it footed to the penny.
7
Sep 2025
Tue 30 Sep
107430.1
103222.6
4207.5
The month after the reconciliation passed. Confidence in the schedule was at its highest here and the quarter closed on a figure that was over four thousand pounds light.
8
Oct 2025
Fri 31 Oct
110864.3
106272.5
4591.8
A Friday, the depot's heaviest day, and the second largest loss in the year. The busier the last day of the month, the more the fence throws away, and month ends land on a Friday twice a year.
9
Nov 2025
Sun 30 Nov
99742
99742
0
The second Sunday month end of the year, and the second month that footed. Two exact months out of twelve is enough to make the schedule look sound to anybody checking one at a time.
10
Dec 2025
Wed 31 Dec
88316.7
85348.4
2968.3
New Year's Eve, a half day, and the quietest month end of the twelve. The depot ran until two o'clock and every one of those consignments was stamped after midnight on the 31st.
11
Jan 2026
Sat 31 Jan
94508.9
91404.2
3104.7
The first of the three months still inside the client's ninety-day re-billing window when the fault was found, and therefore the first of the three that can still be invoiced.
12
Feb 2026
Sat 28 Feb
90137.4
86881.3
3256.1
A short month ending on a Saturday. The missed day is a smaller share of a smaller month, which is exactly the shape that keeps a percentage check quiet.
13
Mar 2026
Tue 31 Mar
112045.6
107395.5
4650.1
The year's busiest month end: 148 consignments between 06:12 and 19:48, worth £4,650.10, every one of them stamped with a time and none of them inside the fence.
14
The year
12 months
1196344.5
1159139.9
37204.6
What the depot carried for this client against what the client was invoiced. Ten of the twelve months are short, the two exact months both ended on a Sunday, and the gap is 3.11% of the year.
15
Still re-billable
3 months
296691.9
285681
11010.9
January, February and March, the only months still inside the ninety-day window in the carriage agreement. The other £26,193.70 is time-barred and will not be recovered.

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 askOn a clean dateOn a 16:42 stamp
What the cell shows31/03/202631/03/2026
=B2=DATE(2026,3,31)TRUEFALSE
=MOD(B2,1)00.695833
=TEXT(B2,"hh:mm")00:0016: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.

Try it in the grid

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.

CheckFormulaKirkburn
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.

Try it in the grid

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 2026Figure
Carried in the month£112,045.60
Recharged to the client£107,395.50
Dropped on the 31st£4,650.10
Consignments dropped148

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.

Try it in the grid

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.

CriterionOn a stamped columnNoticed
=SUMIFS(Net,Stamp,H2)0.00The first morning
">="&H2 with "<="&EOMONTH(H2,0)3% lightNever
">="&H2 with "<"&EOMONTH(H2,0)+1Correct—

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.

Try it in the grid

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:42Result
=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.

Try it in the grid

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 grid

09Stamps 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.

Try it in the grid

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 upWhat goes wrongThe fix
PivotTable rowsOne row per stampGroup ▸ Months
XLOOKUP on a date#N/AMatch on INT
Conditional formatRule never fires=INT($B2)=TODAY()
Data validationEvery date rejectedFence 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.

Try it in the grid

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.

Try it in the grid

12Eight Traps

  1. Formatting the column as a date. It changes what you see and not one comparison underneath it.
  2. "<=" on the last day. It stops at midnight, and midnight is where the day starts rather than where it ends.
  3. ROUND instead of INT. Everything after noon moves to the next day, and the total is wrong in both directions at once.
  4. A criteria cell carrying its own time. The fence is right and the bound is eight hours late.
  5. NOW() where TODAY() was meant. Today's total shrinks every time the sheet recalculates.
  6. Pasting the helper column as values. It stops following the export the first time the export changes shape.
  7. Trusting the filter. The tick list groups stamps by day; SUMIFS compares the number, and the two disagree.
  8. Spot-checking one month. August footed because the 31st was a Sunday, and a sample of one proved the other eleven nothing.

13Five Exercises

  1. 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.
  2. Count the times. =SUMPRODUCT(--(MOD(B2:B21,1)>0)) should report every stamped row before you change anything.
  3. Fence it. Swap the upper bound for "<"&EOMONTH(H2,0)+1 and watch the total move by exactly one day of trading.
  4. Break it with ROUND. Add =ROUND(B2,0) as a helper column, total on that instead, and find the afternoon in the following month.
  5. Fix the source. Load the same rows through Power Query, add Date Only, and check that the exact-match SUMIFS now 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.

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