Split overnight timesheet hours by work date, check automatic clock-outs, and reconcile the pieces with payroll before using them in an estimate. A job that starts at twenty to ten at night can still be open when the clock turns over, and a technician who never clocks out after the last ticket gets closed out at a time nobody picked. Either way, the timesheet has to decide which calendar day owns which hours. That decision changes what each day's report shows, how much labor a day appears to carry, and whether an estimate built from those hours will reconcile with payroll.
Estimators and owners who set crew labor hours from these timesheets usually find the problem late. A job's hours look too high, a day looks too short, or the payroll total does not match the sum of the estimate lines. Midnight is often the cause, because the software has to pick a side of the date line for every minute, and the pick is not always the one you would make by hand.
The fix is a sequence rather than a single formula. Find the events that cross midnight, split them at the right clock times, decide what the leftover minute and the open clock-ins mean, check the result against payroll, and only then turn the numbers into hours you can use in a bid.
This post uses the midnight rules in ServiceTitan's technician timesheets as the worked example, because those rules name two specific clock times: 11:59 PM and 12:00 AM. If your field-service software handles midnight differently, the steps still apply, but swap in your system's rule and test it on a known night before you trust the output.
Check the event fields before exporting
Start with the individual events, not a daily total. ServiceTitan's documentation defines the unit this way: "Each block of time on a timesheet is called a timesheet event." A daily total hides the boundary you need to see, so check which event-level fields your report exposes, including dates and start and end times.
Export the full pay period rather than one day. A job that starts on a Tuesday evening may close out Wednesday morning, and you need both halves in the same file to see them as a pair. ServiceTitan notes that at the end of a pay period, you can run timesheet reports to calculate technicians' work hours and hourly pay for payroll. Use the same date range as that payroll run so you can check the split pieces against it later.
Keep at least these columns for each event: technician, event type (job or non-job), start time, end time, and job number when there is one. You will add three more columns in the next steps.
Confirm the export shows dates as well as clock times. An export with only times cannot tell a start at ten at night on Monday from one on Tuesday. If the date is missing, the rest of the process will look correct and still be wrong, so fix the export format first.
Flag every event that runs past the 11:59 PM line
Scan the export for events where the start and end fall on different calendar days, and flag them. Do not do any arithmetic yet. The point of this pass is to find boundary cases and count how many each technician has. A crew that works late every night will have many; a crew that clocks out on time will have few.
The first midnight time comes from ServiceTitan's rule for work still in progress at midnight: "If at midnight, a technician is working a job or is on a paid non-job event, they're automatically clocked out at 11:59 PM for that day and automatically clocked in at 12:00 AM for the next day."
Flag both pieces and confirm they pair up. An 11:59 PM end with no matching 12:00 AM start on the next day may mean the export is incomplete or the event was edited after it closed. Pull that technician's event history before going further. Do not guess at the missing half, and do not assume the job ended at 11:59 PM just because the system recorded that time.
Add three columns that make the split visible
Add a column for the work date each piece counts toward, and a column for the minutes in that piece. These columns let you reconcile without deciding, row by row, which day a piece belongs to. A third column with the original event ID lets you trace each piece back to its source.
For a job that crosses the boundary, the minutes up to 11:59 PM count toward the first work date, and the minutes from 12:00 AM onward count toward the second. The section on the missing minute explains how to handle it.
Record the split in the sheet, not in your head. A shared sheet with the original event ID, both pieces, the work dates, and the convention you used lets another estimator check your work without re-running the export. It also answers the question someone will eventually ask: why does a job's Tuesday figure differ from its Monday figure?
Treat the 12:00 AM rule as a separate case
The second midnight time works differently. ServiceTitan's documentation says: "If a technician is not clocked out at the end of the day, they are automatically clocked out at 12:00 AM." Check whether this was a clock-in left running or work that continued through the night.
This case can inflate hours. Take a simple example: a technician finishes the last job at half past five in the evening and never clocks out. The system closes the open event at 12:00 AM. That leaves about six and a half hours, from half past five to midnight, that were probably not job work. If you take the closed event at face value, you add those hours to a job or a day that did not earn them. Repeat that across a crew and a few nights a month, and the labor line drifts upward with no matching work behind it.
Flag every event that ends at 12:00 AM and has no job attached, or has a job that ended well before midnight. Compare each one with the last job that technician closed that day and with any notes or photos the crew left on the ticket. Where the timeline shows a long idle stretch, mark that time as not counted for estimating, and record who confirmed it and how.
Do not treat every 12:00 AM close as an error. Some technicians do work past midnight on a real job, and a non-job event can run that late too. The test is whether the record supports the time, not whether the time looks unusual.
Decide what the one missing minute means
Between 11:59 PM and 12:00 AM there is one minute that neither piece covers. Choose a convention and write it down. The options are to attach the minute to the first day, attach it to the second, or drop it. On a single night the choice barely matters. Across a crew, many nights, and a full pay period, one-minute differences add up and can keep a day's total from matching payroll by a few minutes you then have to explain.
The convention I recommend is to attach the minute to the piece that was in progress at 11:59 PM. That keeps each job's elapsed time whole, which is what a job estimate needs, and it is easy to explain to a reviewer: the job ran continuously, so its time is continuous. Whatever you pick, apply it to every event so the crew is treated the same way on every night.
Expect the gap in every split. It follows directly from the two times in the rule, so it is not a data error to hunt for. Planning for it up front is far easier than explaining it after a payroll mismatch.
Keep one distinction clear. The one-minute convention is a reconciliation choice for your estimating sheet, not a claim about how payroll rounds time. If your payroll provider rounds hours on its own schedule, compare the split against the raw minutes in the payroll report and note any rounding separately.
Work through one split by hand
Before you trust a script or a spreadsheet formula, run one split by hand. The dates, spans, and durations below are illustrative. A job starts at twenty to ten on a Monday night and ends at a quarter past one on Tuesday morning, so the elapsed time is three hours and thirty-five minutes.
The total is one minute short of elapsed time because of the 11:59 PM to 12:00 AM gap. If your convention attaches that minute to the first piece, Monday's part becomes two hours and twenty minutes, and the total matches the three hours and thirty-five minutes on the clock.
Run a second case the same way. Suppose a job starts at ten fifty at night and ends at twenty minutes past midnight. The first piece runs from ten fifty to 11:59 PM, which is one hour and nine minutes. The second runs from 12:00 AM to twenty minutes past, which is twenty minutes. The counted total is one hour and twenty-nine minutes against one hour and thirty minutes elapsed, with the same one-minute gap. If your hand split and your script disagree on this case, the script has a bug. Fix it before you run a full pay period.
Split paid non-job events the same way
ServiceTitan's midnight rule also covers paid non-job events, as its technician timesheets overview explains. Apply the split to a truck restock, a shop block, or a training session only if it is paid and runs past midnight. Apply the flagging, splitting, and missing-minute rules to these events too, and tag them as non-job so they do not blend into job labor.
Keeping them separate matters for estimating. Non-job time is usually not tied to one job, so it belongs in its own line when you roll up crew hours. If it sits in the job column, a job estimate can carry hours that were never spent on that job, and the next bid built from that job's history inherits the error.
Gaps between events are a separate question. ServiceTitan's own example in its technician timesheets overview describes a technician who "finishes a job and has 25 minutes before he is dispatched to the next job." Those 25 minutes sit between two events, so they appear in neither job's split unless something covers them. Check how your crew logs that time. If it is a non-job event, tag and count it. If there is no event at all, treat it as idle time, not part of either job's labor, and confirm with payroll how that time is paid before it goes into an estimate line.
When two technicians work the same job across midnight, each has their own pair of pieces. Sum the job's hours by technician first, then by job, so you can see who carried the job past midnight and whether one person's late evening drove most of the total.
Reconcile the split pieces against the payroll report
Sum the split pieces by technician and by work date, then compare the totals with the timesheet report for the same pay period. Compare hours, not dollars. Pay rates change and overtime rules vary, so a rate difference can hide an hours difference. The hour totals should agree within the one-minute convention you chose. Check a larger difference against raw payroll minutes and any rounding before changing the split.
Check three things in this order. First, duplicated pieces: did an export or a copy-paste put the same part in twice? Second, open clock-ins from the 12:00 AM section that were closed at midnight and then counted as work. Third, non-job events you did not tag. Fix each one, then re-run the sums. Do not adjust the payroll side to force a match. The payroll report is the reference, and your split has to meet it.
Keep a short log of every difference and its cause. After two or three pay periods, the log will show which cause keeps returning, and that tells you where to tighten clock-out habits or the export process.
Turn the split hours into crew labor for the estimate
With clean hours for each technician and work date, you can set labor for an estimate. The main risk is double counting. A job that runs from one day into the next has two daily pieces, and both belong to the same job. For the job's labor line, use the total of all its events, counted once, grouped by the job number column. Use the daily split only when you need a daily view, such as checking whether a crew's day is overloaded or scheduling the next morning's labor.
Here is how the two views fit together. Suppose one technician works the job from the worked example above. For that technician, the job's elapsed time and labor line are three hours and thirty-five minutes with the missing minute attached. With that convention, Monday's day view shows two hours and twenty minutes, and the Tuesday view shows one hour and fifteen minutes. Those day figures are useful for capacity. Adding them into the job estimate a second time would overstate the job's labor.
Build a final check into the estimate. Reconcile all job labor lines and non-job lines for the pay period with the payroll total, accounting separately for paid idle time and any rounding difference. If it still does not match, check for a job line counting a piece twice or a piece missing from the job lines. Find that difference in the spreadsheet, before the estimate reaches a customer or a future bid, because a mismatch that reaches the bid is much harder to explain.
Write down the convention and keep the trail
Record three things with the estimate file: which midnight rules your system applies (in this example, 11:59 PM and 12:00 AM), the one-minute convention from the missing-minute section, and the export and payroll report you reconciled against. Include the pay-period date range. A later reviewer should be able to rebuild your labor numbers from those records without asking what you meant.
Keep the records for as long as your payroll and tax obligations require. This post does not set a retention period for your business. Your accountant or payroll provider can tell you what applies to you, and it is worth asking before you clear old exports.
Finally, test the whole process once before relying on it. Pick one overnight job and one open clock-in from the last pay period. Trace each one through every section above by hand, and confirm the result matches the payroll report. If it does, run the process on the next pay period with the same checks. If it does not, the mismatch will show which section needs fixing before the numbers reach an estimate.