Adding hours in Google Sheets helps you track time, calculate payroll, and analyze schedules without leaving the spreadsheet. This guide walks you through straightforward methods so you can sum durations, avoid errors, and keep your data ready to share.
Below is a quick reference that shows common techniques, expected outcomes, and useful tips for adding hours using built-in tools and custom formats.
| Method | Formula Example | Time Format | Best For |
|---|---|---|---|
| Simple SUM | =SUM(B2:B5) | h:mm | Adding a range of time entries |
| Manual TIME VALUE | =TIME(3,30,0) | h:mm:ss | Adding fixed hours without a cell |
| Add hours to a start time | =B2+TIME(2,0,0) | h:mm | Shifting clock times forward |
| Decimal to hours | =A2/24 | h:mm | Converting numeric hours to time |
Enter and format time values correctly
Start by entering hours using a consistent time format such as h:mm or [h]:mm. Use the Format menu and choose Number, then Time or Custom time format. The [h]:mm format is especially helpful when the total exceeds 24 hours, because it prevents the display from rolling over.
If you paste time data from other sources, verify that Google Sheets recognizes it as a time value. Cells should align to the right and respond to time-based formulas. Fix misaligned entries by reapplying the correct time format or by using TIMEVALUE for text strings.
When you plan your sheet, reserve a column for raw time entries and another for calculated totals. Keeping raw inputs separate makes it easier to audit, share, and update your data without breaking formulas.
Sum a range of hours using SUM
The SUM function is the most direct way to add hours in Google Sheets. Select a cell below or to the right of your time entries and type a formula like =SUM(B2:B10). The function adds all valid time values and returns the total in your chosen display format.
Handle blank and error cells
SUM ignores blank cells, but errors in the range will cause the entire formula to fail. Use filtering or IFERROR to manage problematic entries. For long datasets, consider combining SUM with FILTER to include only rows that meet specific conditions.
If your times span multiple days, ensure the format includes square brackets, like [h]:mm. This tells Google Sheets to count elapsed hours rather than treating 24 hours as a full day cycle.
Add fixed hours with the TIME function
To add a specific number of hours without referencing another cell, use the TIME function. For example, =TIME(3,0,0) represents 3 hours, and you can add it directly to a start time cell.
Combine with existing timestamps
When you add hours to a full timestamp, such as 2024-01-01 08:30, use =B2+TIME(3,0,0). The result shifts the clock forward while preserving the date portion. This approach is useful for adjusting schedules, deadlines, or shift rosters.
For projects billed in fixed blocks, store the hours as a decimal in a helper column and convert them using =A2/24. This keeps calculations transparent and lets non-technical teammates edit numbers without touching formulas.
Add hours based on conditions with SUMIF
When you need subtotals for specific categories, SUMIF becomes powerful. You can add hours only for a particular project, team member, or status by defining a condition and a matching range.
Use SUMIFS for multiple criteria
For more control, SUMIFS lets you stack conditions, such as adding hours for a certain project within a specific date range. Place your criteria in alternating ranges and test them step by step to confirm correct results.
Visualize the output by inserting a simple chart linked to your SUM results. A bar chart of daily totals makes it easy to spot peaks, validate schedules, and communicate findings to stakeholders.
Key practices for reliable hour calculations
- Use consistent time formats across all entries
- Separate raw inputs from calculated totals
- Apply [h]:mm format when totals may exceed 24 hours
- Leverage SUM, SUMIF, and TIME for different scenarios
- Test edge cases such as overnight durations and empty cells
- Document your formulas in a nearby cell for future editors
- Share with view or comment permissions to reduce accidental edits
FAQ
Reader questions
Why does my total show a date instead of hours after summing?
Your cell format is likely set to a standard date or time that rolls over at 24 hours. Switch the format to [h]:mm to display elapsed hours correctly.
Can I add hours from filtered rows only?
Yes, use SUBTOTAL with function number 9 or SUMIF with criteria matching the visible labels. SUBTOTAL ignores hidden rows, so it works well with filtered data.
How do I add hours from a time entered as text?
Convert text to a real time value using TIMEVALUE, then apply the desired time format. If dates are missing, TIMEVALUE assumes the current date, which usually does not affect hour totals.
What happens if my total exceeds 24 hours and I do not use brackets?
Without square brackets in the format, 25 hours may display as 01:00. Use [h]:mm to show the full duration accurately.