IF Statements

[italy16]

Hello - this is a great forum, can anyone assist with the following?

Assumption

5% Raise

if date of termination is blank and hire date is before 7/1/17, then 5%, if date of termination is blank and hire date is b/w 8/1/17 and 3/31/18, then 3/31/18 minus 7/1/17 divided by 12 x 5% (basically pro-rata of the 5%), if termination date = any value, then 0

Column A

Column B

Column C

Hire Date

Termination Date

raise %

6/1/15

10/1/17

6/1/16

8/17/16

<tbody>
</tbody>

 

[Robby19]

Re: IF Statements, Thank you

For the first argument, will the date ALWAYS be 7/1/17? If it is susceptible to change I would recommend placing this specific date in a cell and defining it with a name. For example, 7/1/2017 in Cell Z1 defined as Hire Date.

Second argument, what is b/w? Same for this argument as well, will the dates ALWAYS be 8/1/17 and 3/31/2018?

 

[italy16]

Re: IF Statements, Thank you

For the first argument, will the date ALWAYS be 7/1/17? If it is susceptible to change I would recommend placing this specific date in a cell and defining it with a name. For example, 7/1/2017 in Cell Z1 defined as Hire Date.

Second argument, what is b/w? Same for this argument as well, will the dates ALWAYS be 8/1/17 and 3/31/2018?

Thanks. 7/1/17 will be susceptible to change. b/w means between. So basically what I'm trying to say is if you are an employee and are hired before 7/1/17 you are eligible for XX % raise. If you are hired between 8/1/17 and 3/31/18 then you are also eligible for a raise but it is only the pro-rata share. and finally if hired after 3/31/18 then no raise . All the dates are susceptible to change. and finally if there is a value in the "termination date" column then it would return 0%.

 

[RasGhul]

Re: IF Statements, Thank you

Try

<date(2017,7,1),b2<date(2017,7,1)),0.05,if(and(b2="",a2>=IF(ISNUMBER(B2),0,IF(AND(A2<DATE(2017,7,1),B2<DATE(2017,7,1)),0.05,IF(AND(B2="",A2>=DATE(2017,8,1),A2<=DATE(2018,3,31)),(DATE(2018,3,31)-DATE(2017,7,1))/12)*0.05))

</date(2017,7,1),b2<date(2017,7,1)),0.05,if(and(b2="",a2>

 

[RasGhul]

Re: IF Statements, Thank you

Disregard post 4 code,

Try

=IF(ISNUMBER(B2),0,IF(AND(A2<DATE(2017,7,1),B2<DATE(2017,7,1)),0.05,IF(AND(B2="",A2>=DATE(2017,8,1),A2<=DATE(2018,3,31)),(DATE(2018,3,31)-DATE(2017,7,1))/12)*0.05))

 

[Robby19]

Re: IF Statements, Thank you

=IF(ISNUMBER(B2),0,IF(AND(A2<DATE(2017,7,1),B2<DATE(2017,7,1)),0.05,IF(AND(B2="",A2>=DATE(2017,8,1),A2<=DATE(2018,3,31)),(DATE(2018,3,31)-DATE(2017,7,1))/12)*0.05))

I don't have time to run through this, however I would advise against using this. As the OP stated above, the dates are subject to change. The best way to do this would be to define 3 separate cells within the workbook and instead of hard writing the dates in the code, have the code reference the appropriate cell/named cell. That way the OP can change the dates at will without having to worry about messing with the formula.

 

[RasGhul]

Re: IF Statements, Thank you

I don't have time to run through this, however I would advise against using this. As the OP stated above, the dates are subject to change. The best way to do this would be to define 3 separate cells within the workbook and instead of hard writing the dates in the code, have the code reference the appropriate cell/named cell. That way the OP can change the dates at will without having to worry about messing with the formula.

Where E2 = Before, F2 = Start Date, G2 = End Date

=IF(ISNUMBER(B2),0,IF(AND(B2="",A2<$E$2),0.05,IF(AND(B2="",A2>=$F$2,A2<=$G$2),($G$2-$F$2)/12)*0.05))