if (date....issue

[bluepenink]

hello guys

any idea how i can patch my formula up?

i tried this formula in column I14, but dont know how to declare for 175, for the 3rd month and make all remaning months be 200

=IF(AND($J$5>=I$10,$J$5<=I$11),"125",IF(MONTH($J$5)-J$11<=60,"150","175"),"200")
^giving an error!

basically, J5 = hire date
I10 = beginning on month
I11 = end of month

so im saying, if hire date is between the "beginning" and "end" of month, the person's quota should be $125, for their second month with the company, it should be $150, third month of the company, it should be $175, all earlier months or others should be $200.

can someone pls help!

 

[Sahak]

Hi Bluepenink,

Try this;

=IF(AND($J$5>=I$10,$J$5<=I$11),125,IF(MONTH(I10)-MONTH(J5)=1,150,IF(MONTH(I10)-MONTH(J5)>1,200)))