Forum Discussion

Copper Contributor

Jan 29, 2022

timevalue and if conflict

Hi team, I am using timevalue() function to retrieve the time from a text string. This works well. The result is a time-format hh:mm:ss . Then I am comparing the result with a fixed "time" (also hh:m...

MVP

Jan 29, 2022

Let's say the string is in A1 and the time in B1.

Round the result of TIMEVALUE to 1 second:

=MROUND(TIMEVALUE(A1),TIME(0,0,1))=B1

(I'll wait for JoeUser2004 to explain, and possibly correct it)

JoeUser2004
Bronze Contributor
Jan 29, 2022
HansVogelaar wrote: ``=MROUND(TIMEVALUE(A1),TIME(0,0,1))=B1``

It is unwise to use MROUND where the second parameter has a decimal fraction (other than a power of 2).

For example, if A1 is ="2021-01-02 21:55" and B1 is the numeric time 21:55, that MROUND formula returns FALSE (!).

The reason is essentially the same as I explained in my response to EBi2022 . TIMEVALUE(A1) returns the numeric value 0.913194444445253, rounded, whereas 21:55 is the numeric value 0.913194444444445, rounded.
- EBi2022
  Copper Contributor
  Jan 30, 2022
  JoeUser2004 and @Hans Vogelaar: Thanks a lot for your thoughts and explanations. This all sounds reasonable when you think about it. However, same as Joe_User explained, I am bit surprised rounding is not done by timevalue(). Also it surprises me that I am encountering this "issue" only now as I am using timevalue() more often. A reason for this is that I use the timevalue() result to map it with a range (e.g. is the result >4:00 AND <=5:00), so it is not becoming obvious immediately.
  I am now using "--TEXT(<text>;"hh:mm:ss")" as you suggested and it works well !
  
  Thanks again for your help and support on such short notice !

Resources