In XLS, how do IF stmt for IF a date is after another date...?

W

wiggledg

In Excel, how can I create an IF statement to return a value or text IF a
DATE is after or before another date? It doesn't seem to recognize dates and
is returning all the same value in the current IF statement I've created.
I'm not sure if it has something to do with formatting but I've changed the
formats to Dates, General, Text, etc and the IF statement still doesn't
work........
 
P

Pete_UK

With Excel dates in A1 and B1 (note: not text items that look like
dates), try this in C1:

=IF(A1>B1,"A1 after B1",IF(B1>A1,"A1 before B1","same dates"))

Hope this helps.

Pete
 
G

Gord Dibben

And make sure that Tools>Options>Calculation is set to "Automatic"


Gord Dibben MS Excel MVP
 
W

wiggledg

Thanks, I'm not sure why a date can't be hard coded into the formula, but it
works now that I have the formula targeted to a cell. Thanks again!!!
 
P

Pete_UK

You could use DATEVALUE("1/1/2007") instead of one of the cell
references to hardcode a date in the formula and to allow it to be
understood more easily, but isn't it easier to refer to a cell that
contains the date?

Hope this helps.

Pete
 
D

Dave Peterson

I'd use =date() to make sure the date was unambiguous.

=if(date(2007,12,25)>a1, ...

(I'm not sure why you'd hardcode both dates in the cell, though.)
 
Top