I would like to numbers formatted as 'durations' together using a formula in a Google Sheets cell. If it was two numbers it would be very simple...

=1+2 would show as 3

but if I do this...

=00:10:00+0:20:00, I get an #error

I've tried =(00:10:00)+(0:20:00) and =DURATION(00:10:00)+DURATION(00:20:00) but they also come out as #error. Looking around the docs and reference material I can't see how you handle durations in this way.

I know I could have each duration in two different cells and use a third cell to add them together but the way my Sheet is organised this doesn't work for me.


2 Answers 2


Format the resulting decimal as DURATION


Using TIMEVALUE as suggested in @TheMaster's answer will convert e.g. 25:00:00 to 01:00:00.

If you don't want that sort of behaviour, but want the hours to continue counting beyond 24 hours, then you can also do:


If you format the cell as DURATION, then this will result in:


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.