I’m working with a Google Sheets document where I track time durations in mm:ss format across different rows. My data looks something like this:
2:15
1:45
0:45
I’m using the SUM(C3:C25) function to calculate the total time. Everything worked fine until my combined duration exceeded 24 hours. Now instead of showing the correct total like 25:30, it wraps around and shows something like 1:30.
What’s the proper way to format cells in Google Sheets so they can display time totals that go beyond the 24-hour limit? I need the sum to show the actual accumulated hours rather than resetting after 24.
This happens because Google Sheets treats time as fractions of a day, so anything over 24 hours wraps back to zero. I hit this same problem tracking project hours across weeks. Here’s the fix: change your cell formatting to show elapsed time instead of clock time. Select your SUM cell, go to Format > Number > More Formats > More date and time formats. Enter [h]:mm in the format field - those square brackets around the h are key. They tell Sheets to count total hours instead of treating it like time of day. Apply this format and your 25:30 should display correctly. The brackets remove the 24-hour limit so you see actual accumulated time.
Had the exact same problem tracking overtime across multiple weeks. Google Sheets treats your hours like clock time, so it resets after 24 hours instead of adding up the total duration. Quick fix: right-click your total cell, go to Format cells, and create a custom format using [h]:mm:ss. Those square brackets are key - without them, Sheets thinks you’re showing time-of-day instead of total elapsed time. Once you apply this, your totals will show correctly no matter how many hours you rack up. It’s just a display change, doesn’t mess with your actual calculations.