I've got a Google spreadsheet that looks like this:

 Start time  |    End Time   |  Time difference
    11:30:00 |    12:45:00   |     1,25     
    09:20:00 |    10:30:00   |  
    11:35:00 |    12:00:00   |    #VALUE!

In the 3rd column, I have a formula that calculates the difference in decimal number. 1st row works - can I make it work with the 2nd row without splitting the cell?

This is the error I get when I hover over #VALUE:

Error Function MINUS parameter 1 expects number values. But '10:30:00 12:00:00' is text and cannot be coerced to a number.

The formula is =(D26-C26)*24, where C26 and D26 are formatted as time, E column is formatted as a number.

  • If you mouseover the #VALUE! cell, what error message do you get? Commented Oct 6, 2015 at 12:06
  • What formula are you using? Is the first row, second column value correct (it has a comma instead of a colon)? Commented Oct 6, 2015 at 12:15
  • @KristijanVlašić: Comments are meant to be temporary. Please edit your question to include this important information.
    – ale
    Commented Oct 6, 2015 at 14:39

3 Answers 3


The error is due to trying to calculate the difference between two times present in the same cell. Use the following workaround that separates the two times using the SPLIT function, calculates the difference, and sums up the result:

=ARRAYFORMULA(SUM(IFERROR((SPLIT(D26, " ")-SPLIT(C26, " "))*24, 0)))

Make sure to format your cells as time and adjust cell references to match your data!


You need to split the content of cells, then do the math (using arrayformula) and finally join again:

=join(char(10), arrayformula(24*(split(D26, char(10))-split(C26, char(10)))))

Here char(10) is the "new line" control character.

The above will preserve the format of the original cells (e.g., time will remain time). Note that the final result (after join) is just a text string, which cell formatting will not change. If you need the result to be formatted as a number, do it explicitly with value:

=join(char(10), arrayformula(24*value(split(D26, char(10))-split(C26, char(10)))))

Remark. Putting two values in one cell is generally a bad idea if you plan to continue calculating with them. It's mostly a thing we do with text or with final output.


 SPLIT(B3, CHAR(10)) -
 SPLIT(A3, CHAR(10)), "#.##")))


  • Most of your answers comes in the low quality posts category. It would be better if you can add some explanation into your answer.
    – serenesat
    Commented Jan 11, 2019 at 17:37

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.