Calculate Working Hours in Google Sheets
August 7th, 2026
Timesheets, SLA clocks, and project plans all reduce to the same spreadsheet problems: how long between two timestamps, how many business days between two dates, and how to turn Sheets’ day-fraction serials into hours you can read. This guide covers TIME, HOUR, MINUTE, duration arithmetic, NETWORKDAYS, NETWORKDAYS.INTL, and WORKDAY with formulas you can paste into a real sheet.
How Sheets stores time
Google Sheets stores dates and times as serial numbers: the integer part is the day, the fractional part is the time as a fraction of 24 hours.
| Human time | Serial (approx.) |
| --- | --- |
| 6:00 AM | 0.25 |
| 12:00 PM | 0.5 |
| 6:00 PM | 0.75 |
| 8 hours | 8/24 = 0.333… |
That is why end - start for two times on the same day returns a fraction, not “8” for eight hours. Multiply by 24 to get elapsed hours.
For broader date construction (DATE, TODAY, DATEDIF, and friends), see Working with Dates in Google Sheets and Normalize Dates with DATEVALUE and TEXT.
Build and split times: TIME, HOUR, MINUTE
TIME builds a time serial from hour, minute, and second:
=TIME(9, 30, 0)
That is 9:30 AM. Useful when inputs arrive as separate numeric columns (hour in A2, minute in B2):
=TIME(A2, B2, 0)
HOUR and MINUTE extract components from a datetime:
=HOUR(A2)
=MINUTE(A2)
If A2 is 2026-08-07 17:45, these return 17 and 45. They are handy when you need to flag night shifts (HOUR(A2)>=22) or round punches to the quarter hour.
Elapsed hours between two timestamps
Same-day shift
Start in A2, end in B2 (both true date-times or time-only values):
=(B2-A2)*24
Format the result as a number (not a duration time format) if you want 8.5 for eight and a half hours. If you leave the cell formatted as time, Sheets will display a clock face instead of a decimal hour count.
Overnight shift (ends next calendar day)
If someone clocks in at 22:00 and out at 06:00 without a date component, B2-A2 is negative. Fix with a day rollover:
=IF(B2<A2, B2+1-A2, B2-A2)*24
Better still: store full date-times so overnight work naturally has B2 > A2.
Display as hours and minutes
Decimal hours in C2 → readable H:MM:
=INT(C2)&":"&TEXT(MOD(C2,1)*60, "00")
Or keep the raw fraction and format the cell with a custom duration format such as [h]:mm (the brackets allow hours to exceed 24 for weekly totals).
Weekly total
Hours already in decimal form in C2:C8:
=SUM(C2:C8)
If columns still hold raw durations (fractions of a day), sum first, then convert:
=SUM(B2:B8-A2:A8)*24
In modern Sheets that array subtraction often works as written; wrap with ARRAYFORMULA if needed:
=ARRAYFORMULA(SUM(B2:B8-A2:A8))*24
Business days between dates: NETWORKDAYS
NETWORKDAYS counts working days from start to end inclusive, excluding Saturdays, Sundays, and optional holidays.
=NETWORKDAYS(A2, B2)
With a holiday list in Holidays!A2:A30:
=NETWORKDAYS(A2, B2, Holidays!A2:A30)
Example: project starts Monday 2026-08-03 and ends Friday 2026-08-14 with no holidays → 10 working days.
SLA style “business days elapsed so far”:
=NETWORKDAYS(A2, TODAY(), Holidays!A2:A30)
Custom weekends: NETWORKDAYS.INTL
Not every team rests Saturday–Sunday. NETWORKDAYS.INTL lets you define the weekend.
=NETWORKDAYS.INTL(A2, B2, 1, Holidays!A2:A30)
The third argument can be:
- A number preset (for example
1= Saturday–Sunday,2= Sunday–Monday,11= Sunday only — check the function page for the full preset list). - A 7-character string of
0/1for Mon–Sun, where1marks a weekend day. Example: Friday–Saturday weekend:
=NETWORKDAYS.INTL(A2, B2, "0000110")
That pattern is Mon–Thu work, Fri–Sat off, Sunday work — adjust the seven bits to match your region.
WORKDAY — land on a business date
WORKDAY answers “what date is N working days after this start?”
=WORKDAY(A2, 10, Holidays!A2:A30)
If A2 is a start date, this returns the date ten business days later (skipping weekends and holidays). Negative N goes backward — useful for “must start by” planning.
There is also WORKDAY.INTL with the same weekend controls as NETWORKDAYS.INTL:
=WORKDAY.INTL(A2, 10, "0000011", Holidays!A2:A30)
Combining hours and business days
Simple business-hour estimate
If you assume 8 paid hours per working day:
=NETWORKDAYS(A2, B2, Holidays!A2:A30)*8
That ignores partial first/last days. For more accuracy, compute full middle days plus partial endpoints:
=MAX(0, NETWORKDAYS(A2, B2, Holidays!A2:A30)-2)*8
+ IF(NETWORKDAYS(A2, A2, Holidays!A2:A30), (TIME(17,0,0)-MOD(A2,1))*24, 0)
+ IF(NETWORKDAYS(B2, B2, Holidays!A2:A30), (MOD(B2,1)-TIME(9,0,0))*24, 0)
That sketch assumes a 09:00–17:00 workday and that start/end fall on working days — tighten the bounds for your policy. Many teams keep it simpler: separate columns for “business days” and “same-day hours” rather than one mega-formula.
Paid hours from a timesheet grid
| | A (Date) | B (In) | C (Out) | D (Hours) | | --- | --- | --- | --- | --- | | 2 | 2026-08-03 | 09:00 | 17:30 | formula |
=(C2-B2)*24
Exclude unpaid lunch (30 minutes):
=(C2-B2)*24 - 0.5
Or lunch as a time value:
=(C2-B2-TIME(0,30,0))*24
Sum the week with SUM(D2:D6). Flag rows that fall on weekends with:
=IF(OR(WEEKDAY(A2,2)>5), "Weekend", "Weekday")
(WEEKDAY with type 2 makes Monday = 1 … Sunday = 7.)
Formatting checklist
- Decimal hours for math and payroll exports: number format
0.00. - Clock display for punches: time format
HH:mm. - Long duration totals: custom
[h]:mmso 30 hours does not wrap to6:00. - Do not multiply by 24 and then format as time — you will double-convert and get nonsense. Either keep fractions and use duration formats, or convert to decimal hours and use number formats.
Common mistakes
- Subtracting times formatted as text.
VALUEor proper time entry matters; see the date normalization post linked above. - Forgetting inclusive behavior of NETWORKDAYS. Start and end both count when they are workdays.
- Holiday ranges with blanks or text. Keep a clean date column.
- Negative durations from reversed in/out or missing overnight logic.
- Using NETWORKDAYS when you needed calendar days — for total days including weekends use simple subtraction
B2-A2or DAYS.