Multiple greater than and less than variables

Hi,

I am trying to work out probationary costs for my work.

For example, in the first month, if he leaves, he will get 0. However if he leaves between 1 month and 6 months, he will get 1 week pay. and anything between 6 months and 4 years he will get 4 weeks pay. Finally after the 4 years, every incremental year worked gains 1 week of payment if he leaves. so if he works for 5 years we pay him for 5 weeks after leaving date, 6years for 6 weeks and so on up until 12 years and then it stops at 12 weeks.

Can anyone create a formula for this.

I know its complicated but I am stumped.

Thanks

Ryan

If you attach a sample workbook, then we can see what data you have and how it is laid out. Details of how to do this are given in the yellow banner at the top of the screen.

Pete

Hi,

sample sheet should be attached now.

Just want in the A Column, to show that the first name has worked there 6 years so he would get 6 weeks worth of pay so the formula would automatically multiply his weekly rate by 6

and then the same for the person below but for 2 years which would be 4 weeks of his pay.

thanks

ryan

Put this formula in A3:

=IF(H3>=12,12,IF(H3>=4,H3,IF(DATEDIF(F3,$G$2,"m")>6,4,IF(DATEDIF(F3,$G$2,"m")>0,1,0))))

then copy down.

Hope this helps.

Pete

The formula above will give you the number of weeks entitlement - if you want the severance pay then just multiply by column D, like this:

=IF(H3>=12,12,IF(H3>=4,H3,IF(DATEDIF(F3,$G$2,"m")>6,4,IF(DATEDIF(F3,$G$2,"m")>0,1,0)))) * D3

Hope this helps.

Pete

You Guys has saved my life, Thanks a lot.

Thanks

Ryan

We are here to help, so it's good to hear that the solution(s) worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete