Adding Durations in Google Sheets – How to Sum Time in a Single Cell

google sheets

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.

Best Answer

=TIMEVALUE("00:10:00")+TIMEVALUE("00:20:00")

Format the resulting decimal as DURATION

Related Solutions

Google Sheets – Sum Cells with Numbers and Text

try this:

=REGEXEXTRACT(B1,"\d+\.?\d+$|\d+")*1+REGEXEXTRACT(C1,"\d+\.?\d+$|\d+")*1

REGEXEXTRACT part of formula "\d+.?\d+$" does this:

\d - match and retun digit from 0 to 9
\d+ - gives one or more digits
. - any symbol
\. - dot
? - makes previous symbol optional (we use it here to match integers)
$ - matches end of string
| - OR

Update 2016/09

In some cases you may need to use different regular expressions and formulas. For example, if we have the text total = $1,734.00 and want to get 1734, we need this formula:

=REGEXREPLACE(F1,"[^\d]","")/100

this formula does the following:

replace all symbols except digits
divide by 100 because we get number 173400 in first operation

Google Sheets – See Which Formulas Reference a Given Cell

Short answer

Use find & replace

Explanation

Press Ctrl + H to open the Advanced Find and Replace dialog
Write A5 on the Find field
Turn on the Also check within formulas option. This will turn on the View All formulas spreadsheet option.
Click the Find button