why are these cells showing as NOT equal

[welchs101]

Hi, enclosed is a file.

I dont understand why cells A2 and B2 are not equal.........

can someone let me know?

[AliGW]

Because they aren't!

Change the cell formatting to general to see why:

Excel 2016 (Windows) 32 bit

	A	B	C
2	43691.19465	43691.19464	FALSE

Sheet: Sheet1

[welchs101]

so i did as you mentioned changed formatting to general and you're correct they are not equal...........but do you know why they are not equal. if you look at the actual contents of the cells themselves......they are equal.

[AliGW]

Nope!

Excel 2016 (Windows) 32 bit

	A	B
2	14/08/2019 04:40:17.4	14/08/2019 04:40:17.0

Sheet: Sheet1

Custom formatting: dd/mm/yyyy hh:mm:ss.0

The answer will lie in where they came from. They were not entered manually, obviously, otherwise there'd be no discrepancy. You can use rounding to overcome issues like this.

[MrShorty]

I don't see anything in the file to tell us "why" they are not equal. Whatever data entry or importation or calculation scheme that generated these two values, they are different by a little less than half a second. Explain the data entry or calculation scheme, and we might be in a better position to say why they are different.

At some point, I expect that your end goal is to figure out how to introduce some kind of rounding function (MROUND() to the nearest minute or second or whatever is appropriate) or test for "difference between is less than threshold" or something to account for the differences between two values that you clearly want to treat as if they are "equal".

[joeu2004]

do you know why they are not equal. if you look at the actual contents of the cells themselves......they are equal.

Not if you format them as Custom m/d/yyyy h:mm:ss.000 . A2 displays 8/14/2019 4:40:17.430 . B2 displays 8/14/2019 4:40:17.000 .

B2 is the exact binary representation of the displayed time. A2 is not (!). It was probably calculated.

BTW, the LEN function does not reflect the length of the date and time values. Those are just for appearances, due to formatting.

Instead, the LEN function sees the actual internal value, formatted to 15 significant digits (rounded). That is, A2 is 43691.1946461806, and B2 is 43691.1946412037.

See the attached file.

[welchs101]

thanks all.

hey, i guess i am missing something because i see the following in BOTH cells

"8/14/2019 4:40:17 AM"


what am i doing wrong to see the difference

[AliGW]

Change the format of the cells to custom: mm/dd/yyyy hh:mm:ss.0

[MrShorty]

i guess i am missing something because i see the following in BOTH cells -- "8/14/2019 4:40:17 AM"

I don't know if this helps, but I think what you are missing is understanding that there is a difference between "what you see" and "the cell's actual value (a double precision floating point number)". I recall some early experience of my own (similar to yours in many ways) while learning spreadsheets that drove this home to me. I don't know how best to explain it, but a key concept for me to learn was that what I see in a cell is not the same thing as the underlying cell value. Further experience over time helped me come to understand different ways that the cell's value can differ from what I see. Dates and times are stark examples of this, as AliGW shows in post #2 where she shows the underlying double precision floating point numbers in those cells (to only 10 digits, so even those displayed values are not exactly the same as the full double precision value of the cell. Joeu shows those in post #6).

Working on understanding the difference between what you see and the underlying cell value could be key to understanding this particular scenario.