dates and if statement work on one sheet, not on another
 

Something's screwy. Two dates in different cells:

a1: 1/1/2003
b1: 10/29/2001

=a1-b1: 429
=b1-a1: -429
=if(a1<b1,1,0) 1

How can the last formula return a 1????

it returns a 0 for me

"John Brown" wrote:

Something's screwy. Two dates in different cells:

a1: 1/1/2003
b1: 10/29/2001

=a1-b1: 429
=b1-a1: -429
=if(a1<b1,1,0) 1

How can the last formula return a 1????

It's odd. I can get a 0 when I run the formula on cells in certain spaces of
the worksheet. But if I run it on other cells, I get a 1. Does format have
anything to do with this? I've made sure all have a cell format of date. It
is one with the asterisk, but I've tried with others and get the same
results. How can I get totally opposite results based on where I enter the
source data?

"bj" wrote:

it returns a 0 for me

"John Brown" wrote:

Something's screwy. Two dates in different cells:

a1: 1/1/2003
b1: 10/29/2001

=a1-b1: 429
=b1-a1: -429
=if(a1<b1,1,0) 1

How can the last formula return a 1????

Your dates aren't really dates--they're text.

And excel will try to coerce anything that looks like a number (and a date is a
number) to a number when you use it in a calculation (a1-b1).

But it's comparing strings in that 3rd formula.


John Brown wrote:

Something's screwy. Two dates in different cells:

a1: 1/1/2003
b1: 10/29/2001

=a1-b1: 429
=b1-a1: -429
=if(a1<b1,1,0) 1

How can the last formula return a 1????

--

Dave Peterson