Excel saying cells not equal but they are...

[gluis20]

I have a problem with a large excel file where I am doing an if statement like

=IF(B2=C2, "Pass", "FAIL")

Both B2 and C2 appear to have the same values but excel returns FAIL. I can fix this, but it is very manual. Excel recognizes the values as equal after I click in cell B2 and press enter. I am trying to figure out a way to do this for thousands of records without having to click in each cell and press enter. Any advice?

Thanks

[martindwilson]

is auto calculate turned on?

[gluis20]

Under Tools -> Options -> Calculation Tab...Automatic is checked off

This issue does seem to be with the calculation, it seems to be that the information in column B is not fully entered into the cell. Almost as if the data appears in the cell but it is not recognized as data.

[martindwilson]

that needs to be on. then click calculate now

[gluis20]

I have tried Calc Now and Calc Sheet and nothing changes

see attached sample

[gluis20]

Column A is the problem in the sample (not column B as previously mentioned)

[Paul]

Hi gluis20,

The problem is that your column A entries are Text (excel isn't seeing them as a day/time value -- notice they are aligned to the left), while column B entries are in fact Date/Time values (notice they are aligned to the right).

To get the entire column A converted quickly, select all of the values, e.g. A2:A100, then click Data -> Text To Columns... -> click Finish (don't change any settings or click Next).

[gluis20]

Awesome! That did the trick!

Thanks Paul

[lilycol]

great it works with excel 2013! thanks!