How to count working hours between two timestamps

A request lands at three on a Friday afternoon and is answered at eleven on the Monday morning. On the wall clock that is 68 hours. Inside a nine-to-five week it is four. Any report, target or invoice that does not say which of those it means is not saying anything.

Counting working hours is counting working days with a finer ruler. Everything that makes day counts disputable applies, plus a new set of edges that only appear once the unit is smaller than a day.

What to take away

The method, step by step

Clip each working day's window to the interval, add up what is left, and ignore everything else. Five steps cover every row in the worked table that follows.

An interval that begins before the window opens contributes from the opening time, not from when it began. An interval that ends after closing contributes to the close and no further. A weekend or a closed day contributes nothing however long the interval sits inside it.

Seven intervals, worked by hand

All on a 09:00 to 17:00 window, Monday to Friday, no closures, everything in one zone. The dates are in March 2027, and elapsed hours sit beside the working hours so the gap is visible.

The second row is the one to remember. Sixty-one and a half hours pass and none of them count, because the interval starts after Friday's close and ends before Monday's open. The last row shows a full week of working hours inside exactly one week of elapsed time.

What to settle before you count

Five inputs decide the answer: the window, the working week, the closure list, the zone and the resolution. Change any one and the count changes. Two people with different windows will disagree on every interval that crosses an opening or closing time, which is most of them.

The window and the working week come from the contract, the service terms or the employer, and Monday to Friday is an assumption. The closure list comes from the body whose hours you are counting, never a general list. The zone is the agreement's or the office's, and the resolution is whatever the report will be judged in.

The window itself is not a natural object. How long a working day runs, and which hours it occupies, is a matter of law, contract and custom that has moved a great deal over time, as the history of working time shows. Treat it as an input to be collected rather than a constant to be assumed.

The same count in a spreadsheet

With the start in A2, the end in B2 as date-time values, the opening time in Open, the closing time in Close, and a closure range in Closures, one formula does the clipping.

=(NETWORKDAYS(A2,B2,Closures)-1)*(Close-Open)
 +IF(NETWORKDAYS(B2,B2,Closures), MEDIAN(MOD(B2,1),Close,Open), Close)
 -MEDIAN(NETWORKDAYS(A2,A2,Closures)*MOD(A2,1),Close,Open)

The result is a fraction of a day; multiply by 24 for hours. Open and Close are times, so 09:00 is 9/24 and 17:00 is 17/24.

The two MEDIAN calls clip the first and last day to the window, while the NETWORKDAYS(x,x) tests handle a start or end on a rest day or a closure. Checked against every row in the table above, it agrees on all of them.

Everything else the formula needs, especially the closure range, is a range you supply. It contains no calendar of its own.

Hours to days, and why the rate is not fixed

A target written in one unit and reported in the other drifts by the difference every time it is converted. The table shows the same hour figures at three window lengths.

"Three working days" and "24 working hours" are the same target only on an eight-hour window. The drift compounds over a chain of conversions. Pick one unit for the document and keep it.

Edges that only appear at hour resolution

Clock changes. On the day a zone moves its clocks forward, local midnight to the next local midnight is 23 hours; when they move back, it is 25. A window defined in local wall-clock time, 09:00 to 17:00, is unaffected: it is still eight hours on the clock.

An interval measured in elapsed time across the change is off by an hour. Which zones observe a change and when is set by law in each place. For the US, NIST describes daylight saving time as the period when clocks are moved one hour ahead.

An overnight window. A shift from 22:00 to 06:00 belongs to two calendar dates. Treat it as two windows, 22:00 to midnight on the first date and midnight to 06:00 on the second, and the method above works unchanged.

Breaks. A lunch hour is either inside the window, in which case it counts, or a closure inside the day, in which case you model it as a second closed span and clip against both. Either is defensible; counting it sometimes and not others is not.

Resolution. A target of four hours measured to the second and one measured to the minute can disagree at the boundary. Decide, and round the same way at both ends of the interval.

What a figure should carry

A figure of 20.5 hours cannot be checked without the window, the week, the closure list and its date, the zone, the resolution, and both timestamps with their offsets. A target it is measured against cannot be fairly enforced without them either.

The day-level decisions underneath all this, and why they belong to the receiving side, are under adding business days and subtracting business days. The coarser unit of working days across a month is where most hour budgets start, and counting between two dates covers the endpoint rules that an hour count inherits.

Common questions

Can the two ends of the interval sit in different zones?

Yes, but convert both ends to the governing zone first, as the first step says. After that the clipping runs on a single clock and the rest of the method is unchanged.

What happens to hours that fall on a closed day?

Nothing. They do not exist for the purpose of the count. A request received on a closed day starts accruing hours at the next opening.

Can I convert a working-hours target to an elapsed deadline in advance?

Only for a specific start moment. Four working hours from Friday 16:30 is Monday 12:30, and from Monday 09:00 it is Monday 13:00. The elapsed equivalent is different every time.

Is a seven-and-a-half-hour day common?

Common enough that the eight-hour assumption fails often. The window is whatever the contract or the employer says, and it belongs in the record next to the count.