Adding multiple times in the same column.

[squirrel32]

Howdy,

I have a small issue where I'm attempting to add up multiple times and can't figure it out. The main problem is it goes over 24 hrs.

The forumula I use to add up 2 different times is TEXT(G4+K4,"h:mm")). I am then attempting to add up all the times in column L however, here's where the problem begins. Since I've formatted the column as text I can't really use =SUM(L:L) or format L as [h]:mm.

Is there a way to add up two different times without formatting as text and then in turn adding up all times in the same column whilst going over 24 hrs.

Thanks.

[DBY]

Original post deleted was subtracting instead of adding.

Maybe just G4 + K4, custom formatted as [hh]:mm. Same custom format for the Sum cell.

DBY

[FDibbins]

Thanks for the title change

and on a side-note...
Please update your profile as necessary to properly reflect your general location. Members may need to tailor answers based on your regional settings - so "Earth" does not help that

[FDibbins]

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So all you really need to to is just a normal + or SUM, then format the cell as time. the total should then work out OK

[squirrel32]

I think because of the way I'm using =TEXT(J5-I5,"h:mm:ss")) as a means of calculating differences between just two times its messing everything up.

For example, if I do =(TEXT(SUM(L4+L5+L6+L7+L8+L10++L11+L12+L13+L14+L15+L16+L17+L19+L18+L20+L21+L22+L23+L24+L25+L26+L27+L28+L29+L30+L31+L32),"[h]:mm:ss")) I get the correct answer of 24:30:00 however,
if I do =(TEXT(SUM(L:L),"[h]:mm:ss")) I get 24:00:00.

Whilst I could carry on getting the correct answer by just adding on +Lxx I'd rather spend a little time to learn truly how to make the formula look a little nicer.

I've attached an example copy of my spreadsheet. The orange box indicates where the formula lies within.

[popipipo]

You cannot calculate text take a look at this

[squirrel32]

Seems to have done the trick! Thanks :D

Time to go study it, thanks again.