Solved: need a magic formula to ..
hello guys
I've attached an excel file because I really need your help.
Here is my situation.
I have plenty employees, and I want to calculate their holiday right for a month.
In that excel you will find out this columns :
-> Contractual Hours -- in this column I have the contractual hours for each agent;
-> Holyday right -- here I need the magic formula ;
-> Hours to work -- here I have the hours which an agent must work to be paid with the full salary, and to get the maximum holiday right for a month which in my country is 1.75 days ;
-> Worked Hours -- here I have the amount of hours worked by each agent the passed month, in this case January.
-> in the Q1 cell I have the amount of working days for the month.
Other things you need to know :
I have agents which with this types of contracts 2,3,4,6, and 8 hours/day.
The amount of "Hours to work"="contractual hours" * "Working Days" with two exceptions:
-> For an agent who has a contract of 6 hours/day the amount of "Hours to work"="contractual hours" * "Working Days"+6 ;
-> For an agent who has a contract of 4 hours/day the amount of "Hours to work"="contractual hours" * "Working Days"+8
To calculate the amount of "Hours to work" I'm using this formula
.=IF(N4=8;N4*$Q$1;IF(N4=3;N4*$Q$1;IF(N4=6;N4*$Q$1+6;IF(N4=4;N4*$Q$1+8;IF(N4= 2;N4*$Q$1)))))
To calculate the "Holiday Right" for a month for each agent I have to follow this steps
1) - verify what contract has each agent
2) - compare the "Hours to work" values with "worked hours" values
I) For 2,3 and 8 hours contracts I have this situations:
a) "Hours to work" > "Worked hours"
-> In this case the formula is
"Holiday right"=(1.75 / "working days") / "contractual hours" * "worked hours"
b) "Hours to work" <= "Worked hours"
"Holiday right"=(1.75 / "working days") / "contractual hours" * "Hours to work"
The formula which I could use for this cases is
II) For an agent with an 4 hours/day contract=IF(($P4>$Q4);(1.75/$Q$1)/$N4*$Q4;(1.75/$Q$1)/$N4*$P4)
a) ("Hours to work"- 8 ) > "Worked hours"
"holiday right"=(1.75/ ("working days-8")) / "contractual hours" * "worked hours"
b) ("Hours to work"- 8 ) < = "Worked hours"
"holiday right"=(1.75/ ("working days-8")) / "contractual hours" * "Hours to work"
formula is :
III) For an agent with an 6 hours/day contract=IF((P7-8>Q7);(1.75/$Q$1)/N7*Q7;(1.75/$Q$1)/N7*(P7-8))
a) ("Hours to work"- 6) > "Worked hours"
"holiday right"=(1.75/ ("working days-6")) / "contractual hours" * "worked hours"
b) ("Hours to work"- 6 ) < = "Worked hours"
"holiday right"=(1.75/ ("working days-6")) / "contractual hours" * "Hours to work"
formula :
=IF((P13-6>Q13);(1.75/$Q$1)/N13*Q13;(1.75/$Q$1)/N13*(P13-6))
Now I want a formula which can group all this cases ... I've tried a nasted if combination ...without succes --the error message was "you used to many arguments for this function"
Now I'm asking you guys for some help.
Reply With Quote
