1

If one sums a number of cells that contains time data, how does one sum it and format it to show hours and minutes and not days? For example if the times 20:20 & 08:10 are summed, the result formatted by hours and minutes is 04:30, what is required is 28:30

How can a cell be formatted to display total hours?

  • You can convert it to minutes then divide into hours again. – Ralphilius May 4 at 9:04
  • hi @Ralphilius, unfortunately converting to minutes cuts of the hours! So, 01:03, just shows as 3 =MINUTE(SUM(C2:C8)) – atreeon May 4 at 9:07
1

Let's say you have these numbers in A1, A2

  1. Apply TIMEVALUE() to B1, B2
  2. SUM all these values then multiply by 24 into B3
  3. Apply this in any other cell: =ROUNDDOWN(B3)&":"&60*(B3-(ROUNDDOWN(B3)))
| improve this answer | |
  • Many thanks @Ralphilius, it is a bit of a pain converting but I guess Google sheets can't differentiate between date/time and just time so that makes sense. It would be good for there to be a separate function but this works so thank you. I had to add a ROUND statement around the whole of the minutes section there for some reason =ROUNDDOWN(C9)&":"&ROUND((60*(C9-(ROUNDDOWN(C9))))) – atreeon May 4 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.