how do I use if formula using dates?

[Guest]

I need use the if function to do the following:
if the hire date is greater than 9/30/2005, then the date should be
2/1/2006, otherwise the date should be 1/1/2006.

I have been using the following, if(A5>9/30/2005, 2/1/2006, 1/1/2006), but
it changes every date to 1/1/2006 including dates greater than 9/30/2005.

Please help!

 

[Rowan Drummond]

You could try:
=IF(A5>DATEVALUE("09/30/2005"),DATEVALUE("2/1/2006"),DATEVALUE("1/1/2006"))
format result as Date

Hope this helps
Rowan

 

[Bob Phillips]

ANother way

=--"2006-01-01"+(A5>--"2005-09-30")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

I like:

=if(a5>date(2005,9,30),date(2006,2,1),date(2006,1,1))

Then I don't have to worry about any settings on any pc.

But you were really checking to see if:
A5 was greater than 9 divided by 30 divided by 2005.

(9/30/2005 looks more like an arithmetic (division) than a date to excel.)

 

[Dave Peterson]

I think that that original expression was mdy, though. (well, if I can trust
that all were like 9/30/2005.)