Changing Hours in working Days

[Nemo]

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

 

[GS]

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?

 

[joeu2004]

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

 

[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'?

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.

 

[GS]

Nemo explained on 3/9/2011 :

]

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

(The payed
overtimes follow another way)

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.

 

[Nemo]

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

 

[Nemo]

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

[...]

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