Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Hours in working Days
Dear All,
I have a problen, that I am not able to solve. At the beginning of the year, employees have the opportunity to choose what to do with their overtimes. It's possible to pay them or to set aside in an extra account,using them as hourly leaves. There are people who have a high quantity of this extra hours, so I have to change the hours on working days, for example: If John Doe has 125 extra hours, in how many days they correspond? 125 hours divided by 8 = 15.625 days (ie 16 days) it would be better if the result was 15.5 days. The result, with the ROUND function can be managed, by excess or defect, except in the case where the result is 0.5 days, ie half a day. In this case, it must remain unchanged. Towards John Doe, I can not remove a half-day (Rounding down) and give him even half a day plus (Rounding up) the result must be 8.5 (eight days and a half). Now, I have used the ROUND function, but probably there is a dedicated function that I do not know. I hope I was clear, otherwise please let me know. TIA Nemo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Hours in working Days
Nemo expressed precisely :
Dear All, I have a problen, that I am not able to solve. At the beginning of the year, employees have the opportunity to choose what to do with their overtimes. It's possible to pay them or to set aside in an extra account,using them as hourly leaves. There are people who have a high quantity of this extra hours, so I have to change the hours on working days, for example: If John Doe has 125 extra hours, in how many days they correspond? 125 hours divided by 8 = 15.625 days (ie 16 days) it would be better if the result was 15.5 days. The result, with the ROUND function can be managed, by excess or defect, except in the case where the result is 0.5 days, ie half a day. In this case, it must remain unchanged. Towards John Doe, I can not remove a half-day (Rounding down) and give him even half a day plus (Rounding up) the result must be 8.5 (eight days and a half). Now, I have used the ROUND function, but probably there is a dedicated function that I do not know. I hope I was clear, otherwise please let me know. TIA Nemo Well, I see some discrepancies here that need to be clarified before we can effectively help you: 1. You state that the 'hours' can be paid OR taken as 'hourly' leaves. This precludes, then, that 'days' aren't a factor and so why are you trying to calc 'days'? 2. You state that these hours are 'overtime'. Are they factored as overtime (ie: time+1/2 OR double-time) OR are the overtime hours converted to regular hours? 3. You're dividing the number of hours by '8', suggesting that a normal workday is 'actually' 8 hours + breaks/lunch. Is this the case OR does this not matter? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Hours in working Days
On Mar 9, 10:45*am, Nemo
d wrote: 125 hours divided by 8 = 15.625 days (ie 16 days) it would be better if the result was 15.5 days. Perhaps "better"; but is it legal? Setting the legal question aside, it appears that you want to round or round down -- not clear which for lack of examples -- to the half- day. What should 127 hours (15.875 days) become: 15.5 (round down), or 16 (round)? If A1 contains the number of hours (e.g. 125 or 127), then one of the following computes the number of days: =ROUND(A1/8*2,0)/2 =ROUNDDOWN(A1/8*2,0)/2 Note that I used "*2" for clarity. The formulas can be simplified algebraically as follows: =ROUND(A1/4,0)/2 =ROUNDDOWN(A1/4,0)/2 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Hours in working Days
On Wed, 09 Mar 2011 15:06:11 -0500, GS wrote:
[...] Well, I see some discrepancies here that need to be clarified before we can effectively help you: 1. You state that the 'hours' can be paid OR taken as 'hourly' leaves. This precludes, then, that 'days' aren't a factor and so why are you trying to calc 'days'? Answer) In this thread, we are taking into consideration only the extra overtime, set in an account,using them as hourly leaves. (The payed overtimes follow another way) 2. You state that these hours are 'overtime'. Are they factored as overtime (ie: time+1/2 OR double-time) OR are the overtime hours converted to regular hours? Answer) Yes, they are overtime hours converted into regular leave hours 3. You're dividing the number of hours by '8', suggesting that a normal workday is 'actually' 8 hours + breaks/lunch. Is this the case OR does this not matter? Answer) The total daily working hours are '8' hours, it doen't matter if thare are some breaks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Hours in working Days
Nemo explained on 3/9/2011 :
On Wed, 09 Mar 2011 15:06:11 -0500, GS wrote: [...] Well, I see some discrepancies here that need to be clarified before we can effectively help you: 1. You state that the 'hours' can be paid OR taken as 'hourly' leaves. This precludes, then, that 'days' aren't a factor and so why are you trying to calc 'days'? Answer) In this thread, we are taking into consideration only the extra overtime This doesn't explain why you're calcing 'days' , set in an account,using them as hourly leaves. IOW, you're implementing a 'Banked Hours' system. This is typical practice for businesses that pay employees by the hour on an 'as needed' basis so that when work is plentiful (a.k.a.: 'in season') the employees can work as much as they want but only get paid the normal weekly hours so that when it's 'off season' they can 'draw' on their banked hours. Often, the workers are laid off and so the banked hours are used to supplement ui benefits. (legalities set aside!<g) (The payed overtimes follow another way) 2. You state that these hours are 'overtime'. Are they factored as overtime (ie: time+1/2 OR double-time) OR are the overtime hours converted to regular hours? Answer) Yes, they are overtime hours converted into regular leave hours 3. You're dividing the number of hours by '8', suggesting that a normal workday is 'actually' 8 hours + breaks/lunch. Is this the case OR does this not matter? Answer) The total daily working hours are '8' hours, it doen't matter if thare are some breaks. joeu2004's suggestions look good to me and so I won't be adding anything here. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Hours in working Days
On Wed, 09 Mar 2011 12:47:11 -0800, joeu2004 wrote:
On Mar 9, 10:45Â*am, Nemo d wrote: 125 hours divided by 8 = 15.625 days (ie 16 days) it would be better if the result was 15.5 days. Perhaps "better"; but is it legal? Setting the legal question aside, it appears that you want to round or round down -- not clear which for lack of examples -- to the half- day. What should 127 hours (15.875 days) become: 15.5 (round down), or 16 (round)? If A1 contains the number of hours (e.g. 125 or 127), then one of the following computes the number of days: =ROUND(A1/8*2,0)/2 =ROUNDDOWN(A1/8*2,0)/2 Note that I used "*2" for clarity. The formulas can be simplified algebraically as follows: =ROUND(A1/4,0)/2 =ROUNDDOWN(A1/4,0)/2 Thanks Joeu, for your prompt answer! Yes, its all legal, by National Contract, signed by National Unions and National Trade Sector Leadership. I tried your functions and only the these work good for my problem: =ROUND(A1/8*2,0)/2 =ROUND(A1/4,0)/2 These ones give me a #Div/0! Error, Anyway, in your example, 127 hours are *15.875* days and they are more close to 16.0 days, instead of 15.5 so I would use the *Round* funtion. Thank you very much to all for your attetion and Help! Have a nice day Nemo |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Hours in working Days
On Wed, 09 Mar 2011 16:43:56 -0500, GS wrote:
[...] This doesn't explain why you're calcing 'days' , set in an account,using them as hourly leaves. IOW, you're implementing a 'Banked Hours' system. This is typical practice for businesses that pay employees by the hour on an 'as needed' basis so that when work is plentiful (a.k.a.: 'in season') the employees can work as much as they want but only get paid the normal weekly hours so that when it's 'off season' they can 'draw' on their banked hours. Often, the workers are laid off and so the banked hours are used to supplement ui benefits. (legalities set aside!<g) [...] joeu2004's suggestions look good to me and so I won't be adding anything here. Thank you GS for your answer....that does "calcing" mean, perhaps calculating? If so, there are a lot of employee, who have many holidays (vacacy days) to enjoy and banked hours coming from overtime, and for the Company all this is a Cost! So, we need the total amount of *days* to enjoy per employee month by month. We are payed monthly and not by hour, of course, there are overtime. Usually, people, who need money, choose the payed overtime system, whereas employee, who need to leave the work, very often, for family reason or personal/medical one, choose the Banked Hours system. All this is legal, by National Contract, signed by National Unions and National Trade Sector Leadership. I clarify all this, because you were interested, even though it's Off Topic :-). So...have a nice time, and thank you for your attention! Nemo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting working hours to days | Excel Discussion (Misc queries) | |||
Making weekend days working days - the system cuts the working tim | Excel Discussion (Misc queries) | |||
Calculating Working Hours, Days and Overtimes. | Excel Discussion (Misc queries) | |||
Working days and hours calculation - request assistance please! | Excel Worksheet Functions | |||
Changing working days | Excel Worksheet Functions |