7 of 7
added 10 characters in body; edited tags
user0
  • 24k
  • 8
  • 42
  • 69

How to calculate with cells that contain two values separated by linebreak?

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.