Calculating time above 24 hours in Google Sheets

Question

I have a Google doc that list a bunch of hh:mm durations in a column, like:

1:30  
1:00  
0:30

I am using SUM(D2:D24) to sum it up, and recently the total hh:mm went above 24 hours, so my total hours surpassed 24 hours and resultantly displays 0:15.

On Google Sheets specifically, how would I format the total Cell to allow it to go above 24:00?

Conceptionconceptual

Add days to your formatting? – Equerry

Format as 'duration' ? – Keeling

I was calculating my from - to hours but to display the correct sum of the duration I had to enter midnight or 2 in the morning as 24:00 and 26:00. Display hour changes it to 00:00 and 02:00. Now my tot sum of the duration works again. – Limbate

Answer 1

try like this:

=TEXT(SUM(A1:A); "[h]:mm")

Bernabernadene

I missed the overflow in a billing spreadsheet. I can literally afford to eat this month because of your answer, thanks you. – Roxana

Answer 2

Select this box were my mouse is at, then time will add up over 24 hr. (it's in dutch)

Declaim

Answer 3

Click on Format, select Number, then "More Formats" (at the bottom) and click on "Custom number format" Now enter "[hh]:mm:ss" or "[hh]:mm" as preferred.

Source: https://spreadsheetpoint.com/add-time-in-google-sheets/#Showing_Time_in_More_than_24_Hours_Format

Alexander

I think its is a better solution than accepted, since it allows e.g. plotting of these values in a graph. – Souza

Answer 4

Change the Number Format to the 7th option within Time:

Mckinnon