Como Somar Horas No Excel Fórmula: O Segredo Que Poucos Usam
Excel time totals are added with a simple formula such as =SUM(A1:A5) or =A1+B1, and the key is to format the result so Excel displays hours correctly when the total exceeds 24 hours. Microsoft's own guidance confirms that summing times works normally, but totals over 24 need a special format like [h]:mm to show the full duration instead of resetting to a clock time.
How the formula works
Excel stores time as a fraction of a day, so 1 hour equals 1/24 of a day, which is why time values can be added like numbers. That means =SUM(A1:A5) is usually the cleanest option for a range, while =A1+B1 is fine for adding two specific cells. In Microsoft's example, =B2+B3 returns the expected duration only after the cell is formatted properly for long totals.
The most important practical detail is the cell format. If you enter 12:45 and 15:30, Excel may show 4:15 unless the result cell uses an elapsed-time format, because the program interprets the value as a clock time instead of a duration. For work logs, project tracking, and payroll summaries, that distinction matters more than the formula itself.
Best formula choices
=SUM(A1:A10)for a block of hours or durations.=A1+B1for adding two cells directly.=B1+TIME(1,30,0)for adding 1 hour 30 minutes to a time value.[h]:mmas the custom display format for totals above 24 hours.
Microsoft notes that AutoSum also works for time totals, so you can use the ribbon shortcut instead of typing the formula manually. The formula Excel inserts is still the same kind of sum, which keeps the method beginner-friendly.
Step by step
- Enter your durations in cells such as A1:A5 using a time format like
hh:mm. - Click the cell where you want the total to appear.
- Type
=SUM(A1:A5)and press Enter. - If the total is more than 24 hours, change the result cell format to
[h]:mmor[h]:mm:ss. - Check that the input cells are real time values, not plain text.
This sequence is the fastest way to avoid the most common error: typing the right formula but reading the wrong display. In practice, users often think Excel miscalculated, when the real issue is only the cell format.
Example table
| Cell | Value | Meaning |
|---|---|---|
| A1 | 08:30 | 8 hours 30 minutes |
| A2 | 07:45 | 7 hours 45 minutes |
| A3 | 09:20 | 9 hours 20 minutes |
| A4 | =SUM(A1:A3) |
Total duration |
| A4 display | 25:35 | Shown correctly only with [h]:mm |
Why totals can look wrong
When Excel shows 01:30 after adding more than a day's worth of hours, it is not losing data; it is wrapping the result like a clock. That behavior is normal for time formatting, and Microsoft explicitly recommends changing the cell format when the sum goes beyond 24 hours. A report with 36 hours of work should be displayed as 36:00, not 12:00, so the formatting step is essential.
Excel is doing exactly what it was designed to do: storing time as a fraction of a day and displaying it according to the chosen format. Once you understand that logic, time totals become predictable and easy to audit.
Common mistakes
One common mistake is typing hours as plain text, which prevents Excel from calculating anything at all. Another is using a standard time format like hh:mm for totals over 24 hours, which makes the result roll over instead of expanding the total. A third mistake is adding mixed values without checking whether they are durations, clock times, or text labels.
- Use a real time entry, not text like "8 hours".
- Use
SUMfor ranges whenever possible. - Use
[h]:mmfor elapsed time beyond 24 hours. - Keep input cells and result cells formatted consistently.
Useful variations
If you need to add a fixed duration to a start time, the TIME function is often the cleanest approach. For example, =B1+TIME(1,30,0) adds 1 hour and 30 minutes to the time in B1 and returns the new time. Microsoft community guidance and third-party tutorials both point to this pattern for schedule calculations and shift planning.
If you are building a timesheet, the same principle scales well across dozens or hundreds of rows. Many teams use a daily total in one column and a weekly total at the bottom, all driven by the same SUM logic and a consistent display format. That setup is simple, readable, and easy to verify during audits.
FAQ
Practical takeaway
The best way to sum hours in Excel is to use =SUM() for ranges, then apply [h]:mm to the total cell when the result can exceed 24 hours. That combination handles most scheduling, payroll, and project-tracking tasks cleanly and avoids the most common display errors.
Everything you need to know about Como Somar Horas No Excel Formula O Segredo Que Poucos Usam
What is the simplest formula to sum hours in Excel?
The simplest formula is =SUM(A1:A5) for a range or =A1+B1 for two cells. Microsoft's documentation shows both approaches and recommends changing the output format if the total exceeds 24 hours.
Why does Excel show the wrong total after 24 hours?
Excel is usually showing a clock-style time format, not a duration format. To display the true total, format the result cell as [h]:mm or [h]:mm:ss.
Can I use AutoSum for hours?
Yes, AutoSum works for hours and minutes the same way it works for numbers. Microsoft's support page specifically notes that AutoSum can total time values and produce the same sum formula automatically.
How do I add 1 hour 30 minutes to a time?
Use a formula like =A1+TIME(1,30,0). The TIME function lets you define the added duration in hours, minutes, and seconds.
What format should I use for work hours?
For daily entry, use a normal time format such as hh:mm. For totals that may exceed 24 hours, switch the total cell to [h]:mm so Excel shows the full elapsed time correctly.
