timevalue and if conflict

EBi2022 

In essence, you are saying:

TIMEVALUE("2021-01-02 4:00") = TIMEVALUE("4:00") returns FALSE (!).

(We can replace TIMEVALUE("4:00") with TIME(4,0,0) or --"4:00" or 4:00 entered into a cell.)

The problem is the binary approximation of the time value in both cases.  We can demonstrate it as follows:

The strings in column B are created with formulas of the form =A2&"", which displays the decimal representation of the internal binary value, rounded to up to 15 signifcant digits.

Note the difference in the last 4 digits.  That is why the comparison fails.

It might help to understand how Excel stores time as a number.  Dates are an integer value, namely the number of days after 12/31/1899.  So, 1/1/1900 is 1.  2021-01-02 is 44198 or 44228, depending on whether that is Jan 2 or Feb 1.  (And I am assuming a default Excel for Windows date system.  The integer value is different for a default Excel for Mac.)  Time is a fraction of 1 day (24 hr).  So 4:00 is the fraction 4/24, which is 1/6 = 0.666666666666667, rounded.

But Excel stores numbers in binary (64-bit floating-point), which has a limited binary precision (the sum of 53 consecutive powers of 2, called "bits").  Most decimal fractions cannot be represented exactly in binary; they must be approximated.  And the approximation of a particular decimal fraction (e.g. 1/6) might vary, depending on the magnitude of the number.  That is why, for example, 10.01 - 10 = 0.01 returns FALSE (!).

The same thing is happening here.  For the time 4:00, Excel can use all 53 bits to represent the decimal fraction.  But for the date/time 2021-01-02 4:00, some of those bits must be used to represent the date (integer part).  Consequently, there are fewer bits to represent the decimal fraction.  So, we lose precision.

Surprisingly (to me), TIMEVALUE simply extracts the binary fraction.  It is equivalent to MOD(TIMEVALUE(...), 1).  That is unwise because the binary fraction of the date/time is often not the same as the binary approximation of the time alone.

We can correct for this in several ways.  And frankly, I'm surprised that TIMEVALUE does not make the correction automagically.  I had assumed it does.  (Thanks for the education.)

One solution:  use TEXT instead of TIMEVALUE.  Namely:

--TEXT("2021-01-02 4:00:00", "h:m:s")

The double-negate converts the text result to a numeric result.  But there is nothing sacrosanct about "--".  Some people prefer 0+TEXT(...) or 1*TEXT(...).  Any arithmetic operation will suffice for the conversion.