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

`````` 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)? 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

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)))
``````

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