Can there be an AND in an excel if statement

[JPL3]

I am trying to use the following code to generate the number of the Fiscal month column in a Pivot table.

= If(Month(SSP5YrRTAHMin[PSH-Date])>=5 And (Month([PUB_PSHISTOR]![PSH-Date])<=12,Month([PUB_PSHISTOR]![PSH-Date])-4,Month([PUB_PSHISTOR]![PSH-Date])+8))

It works great in ACCESS as an IIF statement, but it says there is a syntax error in my Yes in Excel 2010 as an If.

Any help would be appreciated.

Thanks jpl

 

[AlphaFrog]

Try something like this...
= If(AND(Month(SSP5YrRTAHMin[PSH-Date])>=5, (Month([PUB_PSHISTOR]![PSH-Date])<=12),Month([PUB_PSHISTOR]![PSH-Date])-4,Month([PUB_PSHISTOR]![PSH-Date])+8))

 

[JPL3]

Tried it and it doesn't work either, says there is a syntax error with a parentheses.

= If(AND(Month(SSP5YrRTAHMin[PSH-Date])>=5, (Month([PUB_PSHISTOR]![PSH-Date])<=12),Month([PUB_PSHISTOR]![PSH-Date])-4,Month([PUB_PSHISTOR]![PSH-Date])+8))

The one before <=12.

Thanks for the response

jpl

 

[T. Valko]

Tried it and it doesn't work either, says there is a syntax error with a parentheses.

= If(AND(Month(SSP5YrRTAHMin[PSH-Date])>=5, (Month([PUB_PSHISTOR]![PSH-Date])<=12),Month([PUB_PSHISTOR]![PSH-Date])-4,Month([PUB_PSHISTOR]![PSH-Date])+8))

The one before <=12.

Thanks for the response

jpl

Try it like this...

=IF(AND(Month(SSP5YrRTAHMin[PSH-Date])>=5,Month([PUB_PSHISTOR]![PSH-Date])<=12),Month([PUB_PSHISTOR]![PSH-Date])-4,Month([PUB_PSHISTOR]![PSH-Date])+8)

 

[JPL3]

JUst tried the new version and get the same error.

The syntax for ')' is incorrect
The calculated column 'SSP5YrRTAHMin[FYmo]' contains a syntax error. Provide a
valid formula.

And it's the same ) that is highlighted, the one before <=12.

I tried moving thing around, but can't get it to work

Thanks for the help.

jpl

 

[T. Valko]

JUst tried the new version and get the same error.

The syntax for ')' is incorrect
The calculated column 'SSP5YrRTAHMin[FYmo]' contains a syntax error. Provide a
valid formula.

And it's the same ) that is highlighted, the one before <=12.

I tried moving thing around, but can't get it to work

Thanks for the help.

jpl

If you remove all of those named ranges and replace them with regular cell addresses the formula syntax is correct.

=IF(AND(MONTH(A1)>=5,MONTH(A2)<=12),MONTH(A3)-4,MONTH(A5)+8)

So, if there's a problem it's with those named ranges.

It looks like you're using structured table references that were introduced in Excel 2007. Personally, I hate those types of references and I NEVER use them so at this point I'm out of suggestions.

 

[JPL3]

Thanks for the help, I got it to work, but the reason it didn't was stupidity on my part, but your suggestions put me on the right track.

Thanks again, it's greatly appreciated.

jpl