# 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.

• If you mouseover the `#VALUE!` cell, what error message do you get? 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)?
– Rubén
Oct 6, 2015 at 12:15
• @KristijanVlašić: Comments are meant to be temporary. Please edit your question to include this important information.
– ale
Oct 6, 2015 at 14:39

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.

``````=JOIN(CHAR(10),
ARRAYFORMULA(24 * TEXT(
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. Jan 11, 2019 at 17:37