I'm trying to figure out what I've done wrong to add multiple drive times together to find the total drive time.
In my sample sheet, I've got 2 travel schedules and using scripts, it's calculating the drive time to each new city in column D and then converting the text (5 hours, 32 mins) to format as duration (5:32).
In the 2nd set, the green schedule, it's both struggling with the 1 day 4 hour drive time but even when I just manually type in 28:00, the calculation in row 13 is not calculating all the durations.
I've been looking over this the past several days and can not figure out what I've done wrong.
https://docs.google.com/spreadsheets/d/13ODUpiUE95qkYh6JsbAqNzUwrdNICEXZ7-59INzF8lM/edit?usp=sharing
Use regexextract() and basic arithmetic, like this:
=sum(E3:E12)
=ifna(regexextract(D3, "(\d+) day")) +
ifna(regexextract(D3, "(\d+) hou") / 24) +
ifna(regexextract(D3, "(\d+) min") / 24 / 60)
See Solution and Working with date and time values in Google Sheets.