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, 2020 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, 2020 at 9:07

1 Answer 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)))
  • 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, 2020 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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