Google-sheets – How to calculate with cells that contain two values separated by linebreak

formulasgoogle sheets

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.

Best Answer

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.

Related Topic