If(And Future date formula not working..please help

Heath Sanders

I cant see it for looking, this formula should work (attached0

Heath Sanders

=IF(AND(([Expected Close Date]1-TODAY()) > 90, [Expected Close Date]1-TODAY()) < 180),"3-6",If(AND([Expected Close Date]1-TODAY()) >181,[Expected Close Date]1-TODAY()) <365),"7-12Mths"))

Mike Wilday

From a quick glance, it looks like you have extra parenthesis around your and statements, which is closing them before you are finished writing them. Give this one a shot. 

=IF(AND([Expected Close Date]1-TODAY() > 90, [Expected Close Date]1-TODAY() < 180),"3-6",If(AND([Expected Close Date]1-TODAY() >181,[Expected Close Date]1-TODAY() <365),"7-12Mths"))

Heath Sanders

Hi Mike,

Showing #Incorrect Argument now ? 

=IF(AND([Expected Close Date]1 - TODAY() > 90, [Expected Close Date]1 - TODAY() < 180, "3-6 Mths", IF(AND([Expected Close Date]1 - TODAY() > 181, [Expected Close Date]1 - TODAY() < 365, "7-12 Mths"))))

J. Craig Williams

Heath,

It does not look like you used Mike's formula

=IF(AND([Expected Close Date]1 - TODAY() > 90, [Expected Close Date]1 - TODAY() < 180  )   , "3-6", IF(AND([Expected Close Date]1 - TODAY() > 181, [Expected Close Date]1 - TODAY() < 365  )   , "7-12Mths"))

The AND's need to be closed properly. Your original formula had extra parentheses after the TODAY() function, closing the AND too early.

If you copy Mike's formula directly, it should work. It did for me.

Craig

Mike Wilday

Thanks, Craig. Yep. Your redesigned formula wasn't closing the AND properly. Try copying and pasting my original formula. Also, compare the differences between yours and mine to see how they are formed. Your formula has the AND's being closed at the very end of the formula. You have to close them immediately after you're done setting up your AND arguments.

Heath Sanders

Thank you again gentleman. 

 

Mike Wilday

You're welcome. Glad we could be of help!