if function

[svokozela]

Hi,
i want to calculate,if a start date is less than or equal to 31/12/2006, then it must be 0, and if the end date is more than or equal to 31/03/2007 then the salary must be divided by 12. how do i calculate this? I have attached an example of this.

[HansV]

Put 31/12/2006 in G2, and 31/03/2007 in H2. The formula in E2 wouid be

=IF(A2<=$G$2,0,IF(B2>=$H$2,D2/12,0))

You can fill this down as far as needed.

Note: since all start dates in your sample workbook are less than or equal to 31/12/2006, all results will be 0. See attached workbook.

[svokozela]

Hi Hans,
It works for some but some of the dates that end on the 31/05/2007, it gives me zero for them. i have higlighted two examples, they should get a bonus because their termination date is more than 31/03/2007. I have attached another one where I calculated the formula you gave me.

Thanks,
Sharon

[HansV]

Your first requirement is "if a start date is less than or equal to 31/12/2006, then it must be 0". In the examples you highlighted, the start dates are 1 July 1996 and 1 May 2006. Both are before 31 December 2006, so according to the first requirement, the bonus is 0. Did you mean "if a start date is greater than or equal to 31/12/2006"? If so, change the formula in H3 to

=IF(A3>=$H$1,0,(IF(B3>=$I$1,D3/12,0)))

and fill down.

[svokozela]

Thanks Hans!