[SOLVED] Excel/Calc gives wrong comparison result (after sending file)

Excel/Calc gives wrong comparison result (after sending file)

In Excel I have hours and minutes in format like 10:00.

=LEFT(SEARCH(":";R1C1)-1) helps to get hour.

I use IF and compare the output with numbers.

But I find that after uploading and downloading file (from email, e.g.) formula checking like 10<8 or 10>20 gives wrong answer. The problem persist in Excel and in LibreOffice Calc. Why so, and and what to change?

Here is the formula I use (to check if the time is between 8am and 8pm, and give either CLOSED or the time):

=IF(LEFT(R1C1; SEARCH(":"; R1C1)-1)<8; "CLOSED"; IF(LEFT(R1C1; SEARCH(":"; R1C1)-1)>20; "CLOSED"; R1C1))

Solution

The problem is that Excel/Calc might interprete the output you get with LEFT as text, not number. Therefore, there is a tweak. Just add +0 to it. Then 10+0<8 or 10+0>20 will give the right answer.

Here is a possible formula with +0:

=IF(LEFT(R1C1; SEARCH(":"; R1C1)-1)+0<8; "CLOSED"; IF(LEFT(R1C1; SEARCH(":"; R1C1)-1)+0>20; "CLOSED"; R1C1))

You might as well use VALUE function to convert text values to number values for number comparisons, without +0 (for reference, similarly without tweaks like concatenating empty string as &"", you can use the TEXT function to convert number to text).