Google Sheets – How to Add Hours to Show Days

formulasgoogle sheets

I have 2 cells in Google Sheets with values:

22:00:00
22:00:00

and I have a cell which has the function sum to sum these values and it shows the value: 

20:00:00

I am guessing it is trying to tell me 1 day + 20 hours, but how can I see the 1 day part?

Best Answer

Doing calculations on date/times is tricky, because there are a lot of other parameters that you usually don't think about. Are the two times in the same time zone, for example? Or on which date are the two times?

I see that you want 22 hours + 22 hours to be displayed as 1 day + 20 hours, which sounds reasonable enough, but that assumption fails as soon as a day is not 24 hours - as it can be, when going from/to daylight savings time.

So I think the best you can get is to show the number of hours (44). However, it does not make much sense to format the sum cell as a date (which date? January 1st?), but as a duration: Format: Number: Duration

Feel free to look at/copy the example spreadsheet I've set up.

Related Topic