0

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.

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

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.

Improve this answer
0 

=JOIN(CHAR(10), 
 ARRAYFORMULA(24 * TEXT(
 SPLIT(B3, CHAR(10)) -
 SPLIT(A3, CHAR(10)), "#.##")))

6

Improve this answer
1
  • 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 Jan 11 '19 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.