Hi everyone,

I was trying to sum up a duration of time but excel is not letting me get the answer I need by using the Sum function. Please see below What I'm trying to achieve.

I have 2 sheets; the first sheet is the area where I analyse the data and the second sheet is where the data is coming from. From the second sheet say in column B2:B1000 is where the duration of time coming from each row in a format of 00:00:00.

In sheet 1, I will have a cell (any cell) where the formula is =sum('sheet2'!B2:B10000) but gives me a result of 00:00:00 even with changing the time format from nearly everything.

I managed to get the result by doing this formula =sum(sheet2'!B2:B1000*24) and that's fine.

But now the problem is, it will give me the Hours but not the minutes, instead it is showing as Hours then fraction of the hour as an example 4.93 as 4:55:38 (HH:MM:SS) as the total duration of time.

For me to achieve the format HH:MM:SS, I will need to apply a formula to another cell say =A2/24 to get the HH:MM:SS and format it.

My question is, is there a formula where I can sum up the duration of time without separately creating another cell to divide it by 24 just to get the format HH:MM:SS?

I have seen many tutorials that the sum function should work to add the times, but it never worked for me.

I managed to achieve it by doing the steps outlined above but trying to eliminate the last step where I need to add another cell to divide the total duration of time by 24 then format the cell to get HH:MM:SS.

Any help is highly appreciated.

Thanks in advance.

## Bookmarks