|
Overtime formulas
I do have a special case when it comes to calculate overtime, I think.
The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T. |
Overtime formulas
This works great the first rows, but when I come to one of the last rows
and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T. |
Overtime formulas
Hi Jan,
If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T. |
Overtime formulas
On 23 Mai, 01:30, "Sandy Mann" wrote:
Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
Hi Jan,
I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
Hi, again. Sorry about my English and my explanation.
You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
Hi Jan,
I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a
virtual Swedish Windows XP on my pc as well as US Windows XP and Office 2007, if I email myself a workbook from either regional setting it translates perfectly between the 2 versions. -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Hi Jan, I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
Thank you for the information Peo. In that case I assume that it may be the
function names being different in the Swedish XL that Jan was talking about - surely it does not translate the function names as well. (I have been assuming from the surname "Thorstensen" that Jan included in one post he/she is Swedish). To Jan: If that is the case then I assume that the Replace technique can be used before the equals' signs are replaced. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a virtual Swedish Windows XP on my pc as well as US Windows XP and Office 2007, if I email myself a workbook from either regional setting it translates perfectly between the 2 versions. -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Hi Jan, I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
The "sen" at the end of his name probably means he is either Danish,
Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. The only problems I ever had was if one builds text strings like {"a","b","c"} in array formulas which in US UK can be used with either semicolon or commas but in Swedish is used with \ replacing the commas as a delimiter. Peo "Sandy Mann" wrote in message ... Thank you for the information Peo. In that case I assume that it may be the function names being different in the Swedish XL that Jan was talking about - surely it does not translate the function names as well. (I have been assuming from the surname "Thorstensen" that Jan included in one post he/she is Swedish). To Jan: If that is the case then I assume that the Replace technique can be used before the equals' signs are replaced. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a virtual Swedish Windows XP on my pc as well as US Windows XP and Office 2007, if I email myself a workbook from either regional setting it translates perfectly between the 2 versions. -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Hi Jan, I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
Thank you again Peo.
Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. Thinking about it after a night's sleep, I assume that the *names* of functions that I type in are compiled into machine code of 1's & 0's when the Workbook is saved, which is the same code regardless of what *language* I typed in. The receiving XL will translate the machine code into its resident language and thus *magically* achieve the translation regardless of the different languages/versions. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... The "sen" at the end of his name probably means he is either Danish, Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. The only problems I ever had was if one builds text strings like {"a","b","c"} in array formulas which in US UK can be used with either semicolon or commas but in Swedish is used with \ replacing the commas as a delimiter. Peo "Sandy Mann" wrote in message ... Thank you for the information Peo. In that case I assume that it may be the function names being different in the Swedish XL that Jan was talking about - surely it does not translate the function names as well. (I have been assuming from the surname "Thorstensen" that Jan included in one post he/she is Swedish). To Jan: If that is the case then I assume that the Replace technique can be used before the equals' signs are replaced. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a virtual Swedish Windows XP on my pc as well as US Windows XP and Office 2007, if I email myself a workbook from either regional setting it translates perfectly between the 2 versions. -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Hi Jan, I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
Hi, again.
I finally made it... Am I good or what?! In Column E for 0%: =MAX(0,MIN(D2+MIN(37.5-SUM($D$2:D2),0),D2)) In Column F; 50% =(E2<7.5)*MIN(4,D2-E2) In Column G; 100% =(F2=4)*(D2-(E2+F2)) Thank you Sandy for helping me on my way. And, by the way I am Norwegian, (and I know very many jokes about Swedish people. But I will not start with that, because Peo can probably the same jokes about the Norwegians ;-) Function names and dilimiters are translated perfectly by Excel. When pasting it from plain text is another thing. If I copied my formula from my Norwegian Excel into plain text, it will look like this: 0%: =STØRST(0;MIN(D2+MIN(37,5-SUMMER($D$2:D2);0);D2)) Sandy, you probably helped me more than you know. Thanks again! Regards Jan "Sandy Mann" skrev i melding ... Thank you again Peo. Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. Thinking about it after a night's sleep, I assume that the *names* of functions that I type in are compiled into machine code of 1's & 0's when the Workbook is saved, which is the same code regardless of what *language* I typed in. The receiving XL will translate the machine code into its resident language and thus *magically* achieve the translation regardless of the different languages/versions. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... The "sen" at the end of his name probably means he is either Danish, Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. The only problems I ever had was if one builds text strings like {"a","b","c"} in array formulas which in US UK can be used with either semicolon or commas but in Swedish is used with \ replacing the commas as a delimiter. Peo "Sandy Mann" wrote in message ... Thank you for the information Peo. In that case I assume that it may be the function names being different in the Swedish XL that Jan was talking about - surely it does not translate the function names as well. (I have been assuming from the surname "Thorstensen" that Jan included in one post he/she is Swedish). To Jan: If that is the case then I assume that the Replace technique can be used before the equals' signs are replaced. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a virtual Swedish Windows XP on my pc as well as US Windows XP and Office 2007, if I email myself a workbook from either regional setting it translates perfectly between the 2 versions. -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Hi Jan, I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
Hi Jan,
I am very glad that you got it all sorted out. Forgive me for being curious about your nationality but your midnight being one hour ahead of mine just got me wondering. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. I finally made it... Am I good or what?! In Column E for 0%: =MAX(0,MIN(D2+MIN(37.5-SUM($D$2:D2),0),D2)) In Column F; 50% =(E2<7.5)*MIN(4,D2-E2) In Column G; 100% =(F2=4)*(D2-(E2+F2)) Thank you Sandy for helping me on my way. And, by the way I am Norwegian, (and I know very many jokes about Swedish people. But I will not start with that, because Peo can probably the same jokes about the Norwegians ;-) Function names and dilimiters are translated perfectly by Excel. When pasting it from plain text is another thing. If I copied my formula from my Norwegian Excel into plain text, it will look like this: 0%: =STØRST(0;MIN(D2+MIN(37,5-SUMMER($D$2:D2);0);D2)) Sandy, you probably helped me more than you know. Thanks again! Regards Jan "Sandy Mann" skrev i melding ... Thank you again Peo. Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. Thinking about it after a night's sleep, I assume that the *names* of functions that I type in are compiled into machine code of 1's & 0's when the Workbook is saved, which is the same code regardless of what *language* I typed in. The receiving XL will translate the machine code into its resident language and thus *magically* achieve the translation regardless of the different languages/versions. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... The "sen" at the end of his name probably means he is either Danish, Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. The only problems I ever had was if one builds text strings like {"a","b","c"} in array formulas which in US UK can be used with either semicolon or commas but in Swedish is used with \ replacing the commas as a delimiter. Peo "Sandy Mann" wrote in message ... Thank you for the information Peo. In that case I assume that it may be the function names being different in the Swedish XL that Jan was talking about - surely it does not translate the function names as well. (I have been assuming from the surname "Thorstensen" that Jan included in one post he/she is Swedish). To Jan: If that is the case then I assume that the Replace technique can be used before the equals' signs are replaced. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a virtual Swedish Windows XP on my pc as well as US Windows XP and Office 2007, if I email myself a workbook from either regional setting it translates perfectly between the 2 versions. -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Hi Jan, I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
Overtime formulas
No problem Sandy at all.
BTW, actually the 50% should be simpler than the previous suggestion. Like this: =MIN(4,D2-E2) and not =(E2<7.5)*MIN(4,D2-E2) This evaluation is not neccasary because it makes no difference. However in the next Column the evaluation (F2=4) does matter; In Column G; 100% =(F2=4)*(D2-(E2+F2)) Well, I guess this is were this threads ends. Thanks and by for now! Regards Jan "Sandy Mann" skrev i melding ... Hi Jan, I am very glad that you got it all sorted out. Forgive me for being curious about your nationality but your midnight being one hour ahead of mine just got me wondering. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. I finally made it... Am I good or what?! In Column E for 0%: =MAX(0,MIN(D2+MIN(37.5-SUM($D$2:D2),0),D2)) In Column F; 50% =(E2<7.5)*MIN(4,D2-E2) In Column G; 100% =(F2=4)*(D2-(E2+F2)) Thank you Sandy for helping me on my way. And, by the way I am Norwegian, (and I know very many jokes about Swedish people. But I will not start with that, because Peo can probably the same jokes about the Norwegians ;-) Function names and dilimiters are translated perfectly by Excel. When pasting it from plain text is another thing. If I copied my formula from my Norwegian Excel into plain text, it will look like this: 0%: =STØRST(0;MIN(D2+MIN(37,5-SUMMER($D$2:D2);0);D2)) Sandy, you probably helped me more than you know. Thanks again! Regards Jan "Sandy Mann" skrev i melding ... Thank you again Peo. Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. Thinking about it after a night's sleep, I assume that the *names* of functions that I type in are compiled into machine code of 1's & 0's when the Workbook is saved, which is the same code regardless of what *language* I typed in. The receiving XL will translate the machine code into its resident language and thus *magically* achieve the translation regardless of the different languages/versions. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... The "sen" at the end of his name probably means he is either Danish, Norwegian or maybe even Icelandic, Swedes use "son". Regardless it translates function names effortlessly as well. The only problems I ever had was if one builds text strings like {"a","b","c"} in array formulas which in US UK can be used with either semicolon or commas but in Swedish is used with \ replacing the commas as a delimiter. Peo "Sandy Mann" wrote in message ... Thank you for the information Peo. In that case I assume that it may be the function names being different in the Swedish XL that Jan was talking about - surely it does not translate the function names as well. (I have been assuming from the surname "Thorstensen" that Jan included in one post he/she is Swedish). To Jan: If that is the case then I assume that the Replace technique can be used before the equals' signs are replaced. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Peo Sjoblom" wrote in message ... It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a virtual Swedish Windows XP on my pc as well as US Windows XP and Office 2007, if I email myself a workbook from either regional setting it translates perfectly between the 2 versions. -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Hi Jan, I just have to thank you so much for your patient. It's almost midnight here, I assume that you are East of me and therefore use semi-colons in your formulas. I sent a spreadsheet to your hotmail address, (it is not a good idea to post a real e-mail address in a public forum because you will be targeted with spam so I have "munged" your address below with X's). The spreadsheet I sent did not bounce so I assume that it arrived but you just could not use it. I will therefore send another using semi-colons and the equals signs replaced with "xyz" To activate the sheet select the range E4:H50, (should be already selected in the spreadsheet), and then select Edit Replace Find What: enter xyz Replace With: enter = then click the Replace All button The names will obviously not be the same but I hope that you can figure it out for the above. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... Hi, again. Sorry about my English and my explanation. You are right that in order to get any overtime (50% or 100%) employees have to work at least 37,5 hrs in one week before getting any extra paid. So skipping Wednesday (because they have a contract for 80% part time on a regular basis) he or she would have to work at least 7.5 hrs (f.ex. on Saturday) before diserving any 50% extra. If they then work more, they get until 4 hrs with 50%, every hour after this, will be rewarded with 100% but only if we are talking about the same day. If she or he then works overtime also on Sunday, starting with 42 hrs earlier this week, she or he will start with 50% for the first 4 hrs. After theese the overtime will be 100% extra for the rest of the overtime that Sunday. I just have to thank you so much for your patient. It's almost midnight here, so I will have to get to bed. I will check out your formulas as soon as I get a chance. (Isn't there a way to have the formulas "translated" automatically if I copy yours into a international worksheet and then paste this into a regular worksheet or something like that? Well I can't even get that to work. What do I do wrong?) Thanks again a million! I am really looking forward to a good solution and I think I soon will see it. /:-) Jan "Sandy Mann" skrev i melding ... Hi Jan, I have changed the formulas to: 0%: =IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4)) 50%: =IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0)) 100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0) But I am obviously still not understanding you: For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Why? Monday to Friday (Missing out Wednesday) totals 29:30 12:30 hours on Saturday brings you to 42 hours so surely the normal overtime rules would apply? The only way that reducing the hours on Friday would affect the overtime in Satuday would be if Saturday's 0% hours were calculated to be only just enough to make the total to date to be 37:30 with the rest paid as overtime but you said: Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday If Saturday's 0% hours were calculated to be just enough to bring the total to date up to 37:30 then reducing the hours Monday - Friday would increase the amount of hours required in Saturday's 0% thus reduce the overtime on that day. Anyway I will send you a sample sheet for you to have a look at. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk wrote in message ps.com... On 23 Mai, 01:30, "Sandy Mann" wrote: Hi Jan, If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. That is what I get. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. change the formulas to: 0% Formula: =IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)) 50% Formula: =IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)) 100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) If it is still not right then post back or send me your e-mail address and I will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... This works great the first rows, but when I come to one of the last rows and I have already worked more than 37.5 hrs, it still returns some 0% hours. If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day, that should give me 7.5 hrs with 0% extra. Every hour worked after full time job (37.5 hrs) shall give at least 50% extra pr hrs. If I work 37.5 hours Monday through Friday, all overtime on Saturday will give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if I work a extra day, 7.5 hrs. If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on, (because it is less this day than 4 hours = no 100% extra here). Can this too be calculated in one formula? Thanks very much for helping! Jan "Sandy Mann" skrev i melding ... Jan, With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10 then: 0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4) 50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0) 100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0) and copy down to row 10. It would be possible to miss out the Hours Worked column and calculate the hours in the forumulas but it would make then considerably longer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Jan T." wrote in message ... I do have a special case when it comes to calculate overtime, I think. The case is this: Company rules says that you are not supposed to have any extra % for overtime before you have 37.5 hrs a week. Then, if you work overtime you get 50% and if you work moore than 4 hours overtime one day you get a double50 %. Say one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs overtime. This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra. But when a person works only 80% or 4 days, he first have to work 7,5 hrs overtime that week to have full time job. After that, overtime is rewarded with 50% extra or 100% extra if moore than 4 hrs same day. What I want formulas for is: 0% 50% 100% 0% is for overtime hours up to 37.5 hours a week, then 50% for additional overtime and so on. For full time workers working 8.5 hours overtime: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 0 4 1.5 5/2/2007 16:00 19:00 0 3 0 if part time (80%) this example should look like this: WorkDate FromTime ToTime 0% 50% 100% 5/2/2007 16:00 21:30 5.5 0 0 5/2/2007 16:00 19:00 2 1 0 The formulas must be able to handle both examples in one solution if possible. Any good suggestions for good formulas is very much appreaciated! Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - It is getting better. It seems like there is still something not right though. I tried different scenarios with Monday throug Friday 08:30 to 16:00. Then 08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00. For Saturday and Sunday to return correct answers, there should not be any 0%. Further it should return 4 hrs with 50% both Saturday and Sunday and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on Sunday. This is a full time scenario. For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not Wednesday). Then I put some overtime on Saturday, say from 08:30 to 21:00 and Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday (realising I have worked at least 37.5 hrs that week) and 4 hrs with 50% and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and 1hrs with 100%. I get wrong in first scenario on Sunday. It returns null 50% but it should return 4 hrs here, right? Also if I work only from 08:30 to 15:00 on Friday, it should return 1 hrs less 100% on Saturday I think. Any idéa? Jan |
| All times are GMT +1. The time now is 05:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com