Working hours Google Sheets: setup and timezone trap

To calculate working hours, Google Sheets treats time as a number with a date format, not as a clock. Every working-hours formula is arithmetic on those numbers. Once that is clear, most strange behavior stops being strange.

Nothing here is computed for you. What follows is the representation, the formulas that work with it, and the two settings that decide whether the answers mean anything.

What to take away

Time as a fraction of a day

MOD(A2,1) returns the time part of a timestamp. INT(A2) returns the date part. Those two are the whole toolkit for splitting a moment into the pieces a working-hours formula needs.

Expression	Meaning

| 12:00 | 0.5 of a day | | One hour | 1/24 of a day |

The MEDIAN idiom is the useful one. Given a value and the two window bounds, it returns the value if it is inside the window and the nearer bound if it is outside. That is exactly the clipping a partial day needs, in one function and with no nested conditionals.

The business-hours formula

With a start in A2, an end in B2, and window bounds in Open and Close:

In Google Sheets, enter the opening and closing times as separate time values. Select the opening-time cell, choose Data > Named ranges, and name it Open; repeat for the closing-time cell and name it Close. Put holiday or closure dates in one range and name that range Holidays using the same menu.

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

Multiply by 24 for a figure in hours. Against an 09:00 to 17:00 window with Saturday and Sunday closed, a span from 15:00 on Friday 5 March 2027 to 11:00 the following Monday returns four hours, against 68 hours of elapsed time.

A span from 17:00 Friday to 09:00 Monday returns zero, against 64 elapsed. Keep those two results as the test, because a formula that mishandles the ends often returns something plausible for the first and nonzero for the second.

A count spanning several months also needs the working days per month total for each month it touches.

The format that stops totals wrapping

A total of 30 hours displayed with an ordinary time format shows as six hours, because the format renders a time of day and 30 hours is one day and six. The number is correct.

Use a duration format, or the elapsed pattern [h]:mm, where the square brackets tell the sheet not to wrap the hours component. Applying it to the total and not to the components is the usual arrangement, and it is worth doing before anyone reconciles a column by hand.

The file timezone

In Google Sheets, set the spreadsheet timezone at File > Settings > General > Time zone. Spreadsheet functions that read the current moment use that setting. An Apps Script project has a separate timezone under Project Settings > Time zone, and the two settings can differ.

The failure mode is quiet: when the spreadsheet and Apps Script project use different timezones, timestamps created or interpreted by the script can be offset. Relative differences may still look right while absolute times are wrong together. Set both zones deliberately and write the intended zone in a cell reviewers can see.

Treat a mismatch between the spreadsheet's timezone and the window's timezone as a bug, not a display preference. Use an IANA time zone identifier, such as America/New_York or Etc/UTC, rather than assuming a fixed offset identifies a region. The list of IANA time zone identifiers explains the distinction.

Typed timestamps have the opposite property. A time typed into a cell is a plain number with no zone at all, so it never shifts, and reading it as a moment requires a zone the sheet does not hold. That is the right behavior for a window definition and the wrong behavior for an event log.

Days that are not 24 hours

Because a sheet stores a timestamp as a plain number, it has no concept of a clock change. Subtracting two timestamps that straddle a transition gives the wall-clock difference, not the real elapsed time, and the two differ by an hour.

For a window that sits in daylight, such as 09:00 to 17:00, this changes nothing: the transitions happen in the small hours. For a window that spans them it matters, and the sheet will not tell you. The transition dates for a region are published by its timekeeping authority, and the United States rules are set out by NIST.

What still has to come from elsewhere

The closure list, the window, and any day whose hours differ from the standard are all data. Pass closures to NETWORKDAYS as a range and keep them in one place, versioned as described under how closure calendars are made.

The wider hour model, including breaks and multiple windows in a day, is under working hours, and the day-level count these formulas rest on is under counting a month. Where the same numbers cross into an interface, the type contract changes and is set out under spreadsheet and interface tools.

Common questions

Why does my total of 30 hours show as 6:00?

The cell is formatted as a time of day. Switch it to a duration or elapsed format. Nothing is wrong with the stored value.

Can I store a window as text like "09:00 to 17:00"?

You can store it, but no formula can use it without parsing. Store two time values and label them, which is shorter and cannot be mistyped in a way that still parses.

Does the formula handle a start on a closed day?

Not by itself. The middle term drops closed days, but the two end terms do not. Clamp the end terms to zero when their date is closed, or handle those cases separately.

How do I check the answer?

Pick a span you can count by hand, such as a single working day from before opening to after closing, and confirm it returns the window length exactly. Then try a span entirely outside the window and confirm it returns zero.