I have a spreadsheet where I’m calculating travel times between different locations. The setup includes two different trip itineraries with driving durations listed in column D. I’m using a script that converts text like “3 hours, 45 mins” into time format like 3:45.
The first itinerary works fine, but I’m having problems with the second one (highlighted in green). When the drive time shows something like “1 day 4 hours”, it doesn’t convert properly. Even when I manually enter 28:00 as the duration, the total sum in row 13 isn’t adding up all the time values correctly.
I’ve been stuck on this for days and can’t figure out what’s going wrong with my duration calculations. Has anyone run into similar issues with time formatting and addition in Google Sheets?
Had the same issue a few months ago tracking project hours across time zones. Google Sheets can’t parse duration strings with days in them - that’s why “1 day 4 hours” isn’t converting properly. Your script probably works fine with hours and minutes but breaks on the day part. When you enter 28:00 manually, format that cell as Duration, not Time. They’re different and it affects how SUM works. Also double-check that none of your duration cells got formatted as text by accident - that’ll definitely mess up your totals. I’d modify your script to convert days to hours first, so “1 day 4 hours” becomes “28 hours” as a string, then let your existing conversion handle it.
Classic Google Sheets duration headache! The problem is that Sheets treats durations over 24 hours differently. Your 28:00 values need a custom format: [h]:mm instead of h:mm. Those brackets around the h let Sheets show hours beyond 24. Make sure your SUM function in row 13 uses [h]:mm too. I’ve seen individual cells format fine but the sum cell reverts to standard time format and wraps back to zero after 24 hours. For your script issue with ‘1 day 4 hours’ text - you’ll need to add logic that parses the day component and converts it to hours first. The script probably only looks for hour and minute keywords and ignores ‘day’ completely.
You’re hitting Google Sheets’ time format quirk - it wraps back to zero after 24 hours. Check your sum formula’s format and switch it to [h]:mm:ss instead of the regular time format. The script issue with “1 day” parsing happens because it doesn’t recognize “day” as a valid time unit.
This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.