Dates in If Function 1

[MaryJoM]

I was trying to create a calculated field that would return the applicable fiscal year. I have a date (ex: 8/1/2003) in one field, and would like to have another calculated field return the fiscal year. So I was trying to say if a date in the first field was greater than 7/1/2003 and less than 6/30/2004, then the value in the second field would be 2004. I can't seem to make this work.

Here's what I was trying: if("purchase date" >=7/1/2003 and "purchase date" <= 6/30/2004, 2004, 0). This doesn't work. Any ideas on what I'm doing wrong?

 

[suesloan]

The problem is that you are assuming the formula sees your numbers (like 7/1/2003) as dates, but they are not being interpreted that way. Use the Date() function to compare valid dates to your purchase date field:

if("purchase date" >=Date(7,1,2003) and "purchase date" <= Date(6,30,2004), 2004, 0)

Sue Sloan
XpertSS.com