How to sum time duration in a complex format

[SalemDarwish]

Hi,

I have a report that shows time like [40536:12:57] where it means [hhhhh:mm: ss] (the number of digits for hours might get increase/decrease). in such cases, how to sum durations.

when I try to do so, excel shows #VALUE.

E.x.: A4 [40536:12:57] + A5 [20261:50:13] = [60798:03:10]

how to sum the time, and make the remain of seconds move to minutes, and minutes to hours. so it result should be [60798:03:10]

Thanks,
Salem

 

[jasonb75]

Something like this perhaps. The cell with the formula uses a custom format of [h]:mm:ss

I should point out that if any cell contains less than 10000:00:00 then this formula will not work correctly. Anything below that will be recognised as a valid time and will need to be added separately.

edit:- formula changed to allow for time entries less than 10000 hours. Must be array confirmed with Ctrl Shift Enter if using excel 2019 or older.

Book1
	A
4	40536:12:57
5	20261:50:13
6	60798:03:10
7	121596:06:20
Sheet1

Cell Formulas
Range	Formula
A7	A7	=SUMPRODUCT(IFERROR(LEFT(A4:A6,LEN(A4:A6)-6)/24+("0"&RIGHT(A4:A6,6)),A4:A6))