Time Changing to Decimal When Used in Formula on Different Sheet

[DaveMac14]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am trying to use a time (11:45 AM) from Sheet 1 in a formula on Sheet 2, but the value is changing from the time number format (11:45 AM) to the general number format (.4895) in the formula on Sheet 2. This is causing problems for me because I am trying to make an IF clause where a time value 11:45 on Sheet 2 equals that 11:45 time value from Sheet 1. But it is saying that the two values are not equal. How can I keep the number format from changing and make the two times equal each other?

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am trying to use a time (11:45 AM) from Sheet 1 in a formula on Sheet 2,
but the value is changing from the time number format (11:45 AM) to the
general number format (.4895) in the formula on Sheet 2. This is causing
problems for me because I am trying to make an IF clause where a time value
11:45 on Sheet 2 equals that 11:45 time value from Sheet 1. But it is saying
that the two values are not equal. How can I keep the number format from
changing and make the two times equal each other?

Numeric comparisons don't depend on the display format of the cells.

Formatting shouldn't change once you set the cell format on the cell in
sheet 2 to time.

It may be that, especially if your value(s) are the results of
calculations, that there is a small rounding error causing the
inequality. It's better in that case to check that the two values are
close enough to be considered equal, even if they're not exactly equal.
For example, to check that two times are within a minute of each other:

=IF(ABS(A1-B1)<TIME(0,1,0), "Yes","No")

Note that 11:45 translates to 0.489583333333333 (to XL's 15 decimal
digits of precision), which would round on the display to 0.4896, not
0.4895, so something else may be going on.