False Negative With "IF" Formula

[skatterbraind]

Hi everyone --

I am running a simple if formula comparing two dates in Cells A1 and B1. Cell A1 has a formula returning a date, and cell B1 has a date that was manually entered.

C1 has the formula =IF(A1=B1,1,2), and the value returned is 2, even though cells A1 and B1 have the same values.

When I replace the formula in A1 with a manually entered date, C1 returns the correct value.

I tried changing my formats in column A to general, text, and date -- none have worked. Thoughts?

 

[p45cal]

A date can also contain time information. Are you sure the calculated date is a whole number? Temporarily change the formats of both dates to General. Are they the same?

 

[T. Valko]

Hi everyone --

I am running a simple if formula comparing two dates in Cells A1 and B1. Cell A1 has a formula returning a date, and cell B1 has a date that was manually entered.

C1 has the formula =IF(A1=B1,1,2), and the value returned is 2, even though cells A1 and B1 have the same values.

When I replace the formula in A1 with a manually entered date, C1 returns the correct value.

I tried changing my formats in column A to general, text, and date -- none have worked. Thoughts?

Post the formula you have in cell A1.

 

[Excelestial]

Although dates are really integers in Excel, I've sometimes found that I've had multiply the date by 1 in order to achieve that underlying integer.
Try =IF(A1*1=B1,1,2)

 

[skatterbraind]

Post the formula you have in cell A1.

=IF(Y4="Sunday",X4,(X4+8-WEEKDAY(X4,1))) -- basically just returns the week-ending date.

 

[skatterbraind]

Although dates are really integers in Excel, I've sometimes found that I've had multiply the date by 1 in order to achieve that underlying integer.
Try =IF(A1*1=B1,1,2)

I was really hoping this one would work, but no dice. Thanks, though!

 

[skatterbraind]

A date can also contain time information. Are you sure the calculated date is a whole number? Temporarily change the formats of both dates to General. Are they the same?

Interesting . . . the formula gives me a value of 40986.5, while the manual date returns 40986 . . .

 

[p45cal]

Interesting . . . the formula gives me a value of 40986.5, while the manual date returns 40986 . . .

so stick an int in front of your formula?

 

[T. Valko]

=IF(Y4="Sunday",X4,(X4+8-WEEKDAY(X4,1))) -- basically just returns the week-ending date.

OK, nothing suspicious looking about that.

What's in X4?

You may as well tell us what's in B1, too.

 

[skatterbraind]

Interesting . . . the formula gives me a value of 40986.5, while the manual date returns 40986 . . .

I figured it out -- thank you! The date that I was comparing was driven off of a weekending date that was driven off of a median date . . . the median was giving the decimal. Entered a manual value for that median date, and voila. thank you, been going crazy with this one!!!