Google Sheets で24時間を超える時間を合計する

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Google Sheets on the web and mobile. The formulas also work in Excel, where the elapsed-hours format is also [h]:mm (Format Cells > Custom).

Why the total “resets” after 24 hours

Sheets stores times and durations as fractions of a day: 12 hours is 0.5, 6 hours is 0.25. Your weekly total of 43.5 hours is stored as 1.8125 (one day plus 0.8125 of a day).

A time format only shows the part after the decimal point, the time of day. 0.8125 of a day is 19:30. A duration format, whose hour code is wrapped in brackets, shows the elapsed hours instead. Google’s Sheets API reference describes [h+] as the “number of elapsed hours in a time duration”.

So nothing is wrong with the data. You’re just looking at it with the wrong format.

Example data

A five-day timesheet in A1:D6. Columns B and C hold real times, and column D is calculated:

Expected results:

Step 1: Calculate each day’s hours, including overnight shifts

In D2, subtract the start from the end, wrapped in MOD:

=MOD(C2-B2, 1)

For a normal day, C2-B2 is already positive and MOD changes nothing. Thursday’s shift starts at 22:00 and ends at 6:00 the next morning, so C5-B5 is −16 hours (−0.6667 of a day). MOD(…, 1) adds a full day back and returns 8:00.

To fill the column with one formula instead of dragging, put this in D2:

=ARRAYFORMULA(IF(B2:B="", "", MOD(C2:C-B2:B, 1)))

If your start and end cells contain both date and time (for example 2026-11-05 22:00 and 2026-11-06 06:00), skip MOD and use =C2-B2. MOD would wrongly cut a shift longer than 24 hours down to its remainder.

Step 2: Sum and apply the Duration format

1. In D7, enter =SUM(D2:D6).
2. Select D7 (and D2:D6 for consistency).
3. Click Format > Number > Duration.

The total now reads 43:30:00. To drop the seconds, apply a custom format instead:

1. Click Format > Number > Custom number format.
2. Type [h]:mm and click Apply.

Other elapsed-time formats you can use:

Step 3: Convert the total to decimal hours, minutes or pay

The value is a fraction of a day, so convert it with multiplication. Format these cells as Format > Number > Number, not Duration:

Decimal hours (43.5):

=SUM(D2:D6)*24

Total minutes (2610):

=SUM(D2:D6)*1440

Pay at an hourly rate in F1 (804.75 when F1 is 18.50):

=ROUND(SUM(D2:D6)*24*F1, 2)

The ROUND matters. Fractions of a day are rarely exact binary numbers, so a value like 43.5 hours can be stored as 43.4999999…, and that difference can show up after multiplying by a rate.

Overtime above 40 hours (3:30 here), formatted as [h]:mm:

=MAX(0, SUM(D2:D6) - 40/24)

Write 40 hours as 40/24, not TIME(40,0,0). Google’s TIME documentation says out-of-range values are “silently recalculated”: TIME(25,0,0) becomes 1:00 AM, so TIME(40,0,0) is only 16:00.

Step 4: Fix totals that come out as 0 (times stored as text)

If =SUM(D2:D6) returns 0 or ignores some rows, the durations are probably text, not numbers. This is common after importing a CSV or pasting from a time-tracking app. Quick checks:

• Text sits on the left of the cell by default. Numbers and durations sit on the right.
• =ISNUMBER(D2) returns FALSE for text.
• =COUNT(D2:D6) is smaller than the number of filled rows.

Fix 1: re-enter as numbers. Select the column, choose Format > Number > Duration, then use Data > Split text to columns (or retype) so Sheets parses the values again.

Fix 2: convert inside the formula. This parses h:mm text, including values over 24 hours such as 25:40:

=SUMPRODUCT(IFERROR(LEFT(J2:J5, FIND(":", J2:J5)-1)/24 + MID(J2:J5, FIND(":", J2:J5)+1, 2)/1440, 0))

With 8:30, 9:15, 25:40 and 0:45 stored as text, a plain SUM returns 0 and this formula returns 44:10 (format it as [h]:mm). LEFT takes the hours before the colon, MID takes the two digits after it, and IFERROR skips blanks.

If you need the whole topic for dates (left-aligned dates, DATEVALUE, text in other orders), see Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates. Most of it applies in Sheets too.

Step 5: Totals per person or per week

SUMIF and SUMIFS work on durations like any other number. Just format the result as Duration. For an employee name in column E:

=SUMIFS(D2:D100, E2:E100, "Maya")

For a weekly total, with real dates in column A and a week-start date in H1:

=SUMIFS(D2:D100, A2:A100, ">="&H1, A2:A100, "<"&H1+7)

Entering durations correctly

How you type a duration decides what Sheets stores:

To turn plain minutes into a duration, divide by 1440: =A2/1440. For plain decimal hours (7.5), divide by 24: =A2/24. Then format the result as Duration.

Common mistakes

1. Using HOUR() on a total. HOUR returns only the hour of the day, so =HOUR(SUM(D2:D6)) returns 19, not 43. Use =INT(SUM(D2:D6)*24) for whole hours.
2. Formatting the decimal-hours cell as Duration. 43.5 formatted as a duration means 43.5 days, shown as 1044:00:00. Decimal results need the Number format.
3. Subtracting overnight times without MOD. =C5-B5 returns −16 hours. Use =MOD(C5-B5, 1), or store full date-times.
4. Building thresholds with TIME(). TIME wraps hours above 23. Use 40/24 for 40 hours.
5. Using TEXT() to format the total. TEXT returns text. In Sheets, TEXT of 43:30 multiplied by 1 converted to 1.8125, but SUM of that text cell returned 0. Prefer a numeric total with a number format; use a separate cell for labels.

FAQ

Why does my time total reset after 24 hours in Google Sheets?

The cell uses a time-of-day format, which only shows the part of the total after full days. Apply Format > Number > Duration or the custom format [h]:mm to show elapsed hours.

How do I convert a duration to decimal hours?

Multiply it by 24 and format the result as Number: =SUM(D2:D6)*24 returns 43.5 for 43 hours 30 minutes.

How do I calculate hours worked across midnight?

Use =MOD(End-Start, 1). If the end time is earlier than the start, MOD adds one day, so 22:00 to 6:00 returns 8:00.

Why does SUM return 0 for my times?

The times are stored as text. Check with ISNUMBER, then re-enter them as durations or parse them with LEFT and MID, as shown in Step 4.

Can I show a total like “43 h 30 min”?

Yes. Use the custom number format [h]” h “mm” min”. The cell stays a number, so you can still use it in other formulas.

Related tutorials

• Calculate Hours Worked Including Overnight Shifts in Excel (Free Timesheet)
• Calculate Age from Date of Birth in Google Sheets (Years, Months, Days)
• Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates

Live-test findings

The same numeric total displayed as 19:30 with h:mm and 43:30 with [h]:mm. Sheets custom formats also returned 43:30:00, 43 h 30 min, and 2610 for [h]:mm:ss, [h]” h “mm” min”, and [m]. Changing the format of existing text 8:30 did not convert it to a number; parsing returned 0.3541666667. Format numeric durations rather than relying on text coercion.

Sources

• Google for Developers: Sheets API date and number formats ([h+] elapsed hours, [m+] elapsed minutes): https://developers.google.com/workspace/sheets/api/guides/formats
• Google Docs Editors Help: Format numbers in a spreadsheet (Format > Number, custom number and date/time formats): https://support.google.com/docs/answer/56470
• Google Docs Editors Help: TIME (out-of-range hours are silently recalculated, TIME(25,0,0) = 1:00 AM): https://support.google.com/docs/answer/3093056
• Google Docs Editors Help: HOUR (returns the hour component of a time): https://support.google.com/docs/answer/3093045
• Google Docs Editors Help: TEXT (supported date and time patterns): https://support.google.com/docs/answer/3094139