Timesheet Checks on Microsoft 365
Every hour traces to a person, a day and an allocated job before it is ever a total. The award lives in the tenant as dated rows, not as a formula somebody wrote in 2024.
Used by
- Business Systems Analyst
- Office Administrator
- Operations Manager
How to get this live in your business
A timesheet tool that adds up is not a check. The check is that the hours belong to somebody who was rostered, on a job they were allocated, at a rate from the edition in force for those dates, and that anything odd sat in front of a named person before payroll ran. Every one of those is a join against a list you already keep. The part Microsoft 365 will not do is the last step, and this page says so rather than drawing a flow that pretends.
In short
What this is
A build plan for the timesheet check standard on Microsoft 365. Eight lists, two libraries, four flows, seven measures.
The core rule
An entry is traced against the roster and the allocations before it is a line. A penalty applied carries a card naming the rule, the edition and the date it took effect.
What it does not do
It never decides which clause covers an odd shift. A situation the edition does not plainly name is parked as rule_unclear, with an owner, and no card is written for it.
The hard part
The award edition that changed between periods, and the scheduled export that ran again because nobody read back whether it already had.
Take the spec to Claude, ChatGPT or Copilot. The sentence to paste is one click away.
Want the whole thing by email?
This paper, plus the rest of the library. One click to stop them.
1. Before you start
Read the timesheet check build standard first. It defines its objects, and the lists below are those objects with SharePoint types on them. Shared conventions are in building the standards on Microsoft 365.
Decide one thing first: where the allocations come from. The standard asks every entry to name a job the person was allocated on that day, and that is a fact the job system holds, not the timesheet app. If the job system can be read on a schedule into an Allocations list, build it there. If it cannot, the honest version is a supervisor keeping allocations in the list by hand, and you should know that before you promise the not_allocated check.
Then settle how the award is held. Not as a formula and not as a rate typed on a person’s record: as rows in a Rules list, one per edition, each with an effective from and an effective to, with the instrument itself in a document library behind it. Everything the standard says about citing an edition and refusing a retired one follows from that shape, and nothing recovers it later.
2. The lists, with their columns
For your developer. Show the fields
Site: TimesheetCheck
LIST Entries (as captured, never edited in place)
EntryKey Text indexed
Worker Person indexed. Not a typed name
EntryDate DateTime indexed
PeriodRef Text indexed
JobRef Text indexed. Blank if none was given
StartAt DateTime
EndAt DateTime
BreakMinutes Number
Hours Number written by the flow. Off every form
Source Choice site | app | paper
CaptureFlags Text what the capture app noticed, in its own words. Mapped, never adopted
LIST Allocations (read from the job system)
Worker Person
JobRef Text indexed
AllocDate DateTime indexed
LIST Roster
Worker Person
RosterDate DateTime indexed
Rostered Boolean
LIST Rules (append only. The award, by edition)
RuleId Text indexed
Instrument Text
Edition Text indexed
EffectiveFrom DateTime
EffectiveTo DateTime blank means in force
Covers Text the situations this edition plainly names
ClauseLink Hyperlink into Instruments
LIST Totals (derived. Never entered)
PeriodRef Text indexed
Worker Person
Scope Choice daily | weekly
ScopeDate DateTime
Hours Number
EntryIds Text the entries this sums. Blank = a typed total
LIST Anomalies
EntryKey Text indexed
Kind Choice over_hours | overlap | missing_break
| not_rostered | not_allocated | rule_unclear
fill-in OFF. Six is the list
Owner Person REQUIRED
RaisedAt DateTime
DecidedBy Person blank until a person decides
Decision Choice approved | corrected | rejected
Reason Text their words, not a code
DecidedAt DateTime
EvidenceLink Hyperlink
LIST Cards (one per rule applied to an entry)
EntryKey Text indexed
RuleId Text indexed
Edition Text
EffectiveFrom DateTime
WrittenAt DateTime
LIST Periods
PeriodRef Text indexed, UNIQUE. One period, one row, refused rather than checked for.
FromDate DateTime
ToDate DateTime
ReleasedBy Person blank until a person releases
ReleasedAt DateTime
ExportExternalId Text payroll's own batch id. Blank is a failure
ExportedAt DateTime
ReportLink Hyperlink every period, including a clean one
LIBRARY Instruments (the award editions as documents)
Instrument Text
Edition Text indexed. what Rules cites and Cards records
LIBRARY PeriodReports (what Flow 1 publishes each run)
PeriodRef Text indexed. what ReportLink points at
The two libraries carry only the key their lists cite them by. The plan
names no other column on either.Worker is a Person column everywhere, not a name in a Text field. Two spellings of one person breaks the join to the roster and to the allocations, makes the owner of an anomaly unaddressable, and makes the by-person record the standard asks for impossible to pull. It also gives row level security something real to sit on later.
3. Every rule, and what enforces it
| The rule | Enforced by | How |
|---|---|---|
| Every entry traces to a person, a day and an allocated job | Flow 1, Allocations | Before anything is totalled, the flow looks for an Allocations row matching the worker, the job and the date. No match is an anomaly of kind not_allocated and the entry never becomes a line. |
| Totals are computed from entries, never typed | Totals.EntryIds | Every Totals row lists the entry ids it sums. A row with a blank EntryIds is a typed total by definition, and the measure that counts them should read zero forever. |
| An anomaly is one of a closed list of kinds, and it has an owner | Choice column, required Person | Six choices with fill-in switched off, and Owner required at the list. A seventh kind is a change to the standard, not something a flow invents at three in the morning. |
| A penalty or allowance cites the rule, its edition and its effective date | Cards list, Flow 2 | The card is written in the same flow step that applies the rule. A line carrying a penalty with no Cards row is the failure, and it is countable rather than arguable. |
| A retired rule edition cannot apply to the next period | Rules.EffectiveFrom and EffectiveTo | Flow 2 selects the edition whose effective window contains the entry date. A retired edition cannot be selected, so the 1 July change happens by loading a row rather than by editing a formula. |
| An entry the rule does not plainly cover is parked for a person | Kind = rule_unclear | No card is written, no penalty is applied, and the entry is not a line. The build never picks between two clauses, because that is the decision that ends up in front of a tribunal. |
| An anomaly leaves the queue only with a name and a reason | Approval, DecidedBy and Reason | The approval outcome is written back as DecidedBy, Decision and Reason. An anomaly with a Decision and no DecidedBy is the record the standard says should not be writable. |
| Nothing reaches payroll without a release by a named person | Periods.ReleasedBy, Flow 4 | Flow 4 checks open anomalies first and ReleasedBy second, and does nothing if either fails. There is no threshold and no rule that can stand in here: this release is always a person's. |
4. The four flows
For your developer. Show the fields
FLOW 1 Trace, total, publish
trigger a recurrence, nightly, over the open period
logic for each Entry: compute Hours from StartAt, EndAt, BreakMinutes
check Roster for the worker and the date -> not_rostered
check Allocations for worker, job, date -> not_allocated
check the other entries that day for a shared minute -> overlap
check the day's hours against the business's own limit
-> over_hours
check a long entry for a break -> missing_break
map CaptureFlags to a kind, or to nothing. Never to a new kind
write Totals rows, daily and weekly, each naming its EntryIds,
from traced entries only
publish the period report into PeriodReports and set ReportLink
never skip the report because the period was clean. A clean period
publishes zeros, so silence never means clean.
FLOW 2 Read the rule, write the card
trigger an Entry is traced and has no open anomaly
logic select the Rules row whose effective window holds EntryDate
if the entry plainly matches a situation in Covers:
apply it, and write a Cards row with RuleId, Edition and
EffectiveFrom in the same step
otherwise raise an Anomaly of kind rule_unclear with an owner
never apply a rule without writing its card, and never fall back to
the previous edition because the current one is silent.
FLOW 3 Decide an anomaly
trigger an Anomalies item is created
logic start an approval to Owner with the entry beside it
write DecidedBy, Decision, Reason, DecidedAt from the outcome
store what they attached and link it in EvidenceLink
never clear an anomaly from a flow condition. The only path out of
this queue runs through a person.
FLOW 4 Release, then export once
trigger an operator action on a Periods item
logic refuse if any Anomaly in the period has a blank DecidedAt
refuse if ReleasedBy is blank
refuse if ExportExternalId already has a value
generate the payroll file, then wait for the batch id to be
written back before setting ExportedAt
never set ExportedAt from the flow's own success. The export exists
when payroll says it does, not when the run goes green.Flow 4 refuses in three places and writes in one. That ratio is the standard: the release, the open anomalies and the second run are the three ways a period reaches payroll wrong, and each of them is cheaper to refuse than to unwind after somebody has been paid.
5. The Power BI model and its measures
For your developer. Show the fields
Exported Without Release = -- must be zero. Front page.
CALCULATE (
COUNTROWS ( Periods ),
NOT ISBLANK ( Periods[ExportExternalId] ),
ISBLANK ( Periods[ReleasedBy] )
)
Typed Totals = -- a total that names no entries
CALCULATE ( COUNTROWS ( Totals ), ISBLANK ( Totals[EntryIds] ) )
Cards On A Retired Edition = -- the 1 July measure
COUNTROWS (
FILTER (
Cards,
NOT ISBLANK ( RELATED ( Rules[EffectiveTo] ) )
&& RELATED ( Entries[EntryDate] ) > RELATED ( Rules[EffectiveTo] )
)
)
Anomalies Open =
CALCULATE ( COUNTROWS ( Anomalies ), ISBLANK ( Anomalies[DecidedAt] ) )
Anomaly Rate = -- put Kind on the axis
DIVIDE ( COUNTROWS ( Anomalies ), COUNTROWS ( Entries ) )
Decision Latency Days =
AVERAGEX (
FILTER ( Anomalies, NOT ISBLANK ( Anomalies[DecidedAt] ) ),
DATEDIFF ( Anomalies[RaisedAt], Anomalies[DecidedAt], DAY )
)
Periods Without A Report = -- should be zero, clean ones included
CALCULATE ( COUNTROWS ( Periods ), ISBLANK ( Periods[ReportLink] ) )Anomaly Rate broken down by Kind is the one to watch over months rather than days. A rising not_allocated line is an allocations problem in the job system, a rising rule_unclear line is an award edition that no longer describes how the business actually rosters, and neither of those is visible from any single period.
6. The order to build it in
- Load the current award edition into Rules as one row with an EffectiveFrom and a blank EffectiveTo, and put the instrument itself in the Instruments library with ClauseLink pointing at it. Until an edition exists, no card can be written and half the standard is untestable.
- Create Entries, Roster and Allocations, and land one real period of entries by whatever route the capture app allows. Paper is a valid Source value; it just means somebody keys it.
- Create Anomalies with the six kinds as a Choice column, fill-in off, and Owner required at the list rather than in the flow.
- Build Flow 1 as trace only: no totals, no report. Prove an entry on an unallocated job raises not_allocated and stays off the sheet.
- Add the totals to Flow 1 and write EntryIds on every row. Add the report last, and confirm a period with nothing wrong still publishes.
- Build Flow 2 and prove the rule_unclear path first: an entry no edition plainly covers must park with no card. Then prove the happy path writes one.
- Build Flow 3 so the approval outcome lands on the row. An anomaly cleared anywhere other than on its own row is not cleared.
- Build Flow 4 last, with the three refusals before the write, then connect Power BI with Exported Without Release and Cards On A Retired Edition on the front page.
7. Four traps specific to this build
Adopting the capture app's flags as the anomaly kinds
Every site app has its own vocabulary for an odd shift, and importing it turns a closed list of six into a Choice column with fill-in on and forty values by Christmas. Keep CaptureFlags as text the flow reads, map each flag to one of the six kinds or to nothing at all, and let a new kind be a deliberate change to the standard.
One current award row that gets edited when the rate moves
Overwriting the Rules row on 1 July destroys the only evidence of what June was paid under, and the standard's fifth check depends on the retired edition still being there and still being unusable. Rules is append only: a new item with a new Edition and EffectiveFrom, and EffectiveTo written onto the one it replaces.
Totals as a grouped view or a calculated column
A grouped view adds up at read time and proves nothing once it is closed, and a SharePoint calculated column cannot list the entries it summed. The standard asks a total to name what it derives from, which means real Totals rows written by the flow with EntryIds on them.
A recurrence-triggered export with no read-back
A scheduled flow runs again by design, and a period that was released on Friday is still released on Saturday. Put unique values on PeriodRef, read ExportExternalId before doing anything, and treat a second release as an update or a refusal. Two batches in payroll is not a flow you can quietly re-run.
8. Where the automation stops
- Every anomaly resolution, with a name, one of approved, corrected or rejected, and a reason in their own words. The queue has no other exit.
- The release of the period to payroll. There is no threshold and no rule that can stand in for it here, which is the one place this build is stricter than the money side of the house.
- Which award applies to the business, and which clause applies to the odd shift. The build reads a dated rule and cites it; it never decides what the rule means.
- Any dispute with the person whose hours they are. The record shows the entry, the cards and who cleared what; a person has the conversation.
- Loading the export into payroll and getting the batch id back. Microsoft 365 will produce the file and hold the queue. It will not tell you the batch landed, so the id is keyed back by whoever loaded it and the period is not exported until it is there.
The pass test. Stage one worker with two entries on the same day that share a minute, and a third entry on a job they were never allocated to. Run Flow 1. You must get three Anomalies rows, each with a Kind from the six and an Owner who is a real person in the tenant, and no Totals row that names any of those three entries. Now try to release the period: Flow 4 must refuse, because anomalies are open. Clear them as a named person with a reason, release, and run the export twice an hour apart. Exactly one Periods row must carry an ExportExternalId. Fifteen minutes on test data. If a Totals row included the overlapping pair, the totalling is running before the trace and every hour in that period is a claim nobody checked. If the export wrote twice, PeriodRef is not unique and payroll is holding two batches, one of which nobody released.