We are trying to create a field to calculate fiscal year of an existing date field.

This is what we came up with:

Case( Year(Date of Intake = "2004" and Month( Date of Intake >= "07" and Month(Date of Intake <= "12"))), "2005" , Year(Date of Intake = "2005" and Month( Date of Intake >= "01" and Month(Date of Intake <= "06"))), "2005" ,Year(Date of Intake = "2005" and Month( Date of Intake >= "07" and Month(Date of Intake <= "12"))), "2006" , Year(Date of Intake = "2006" and Month( Date of Intake >= "01" and Month(Date of Intake <= "06"))), "2006" )

Can anyone tell us what is wrong?

Thanks!

You should make your calc more general, so it can be used with other years as well.

Year and Month functions return numbers, so quotes aren't necessary and probably won't work with the single digit months with a leading zero either. You're also missing closing parentheses around Year and Month in all of your tests. They should look like Year(Date of Intake) or Month(Date of Intake).

That aside, you can use a simple Year(Date of Intake) + (Month(Date of Intake) > 6), and not have to hardcode anything. Enclosing the Month(Date of Intake) > 6 test in parentheses forces it to act like a boolean, returning 1 when true and zero when false, effectively adding 1 year if the month is greater than 6.