Overtime

[puiuluipui]

Hi, i need an overtime formula with a difference of +/- 10 minutes, like in
the below example.

Ex:
Work hour : 08:00:00
08:00 - 16:25 overtime = 00:25 result(00:30)
08:00 - 16:19 overtime = 00:19 result(00:00)
08:00 - 16:50 overtime = 00:50 result(01:00)
08:00 - 16:49 overtime = 00:30 result(00:30)

I need the code to calculate from 30 to 30 minutes. But with a difference of
+/- 10 minutes.
If overtime is between 20 and 30, then the result of the code to be 30
minutes.
If overtime is between 50 and 60 (59:59), then the result of the code to be
1 hour

Can this be done?
Thanks!

 

[Luke M]

Assuming you've already calculated the amount of overtime in cell C2
(something like =a2-a1-"8:00")

The formula you need for desired result is:
=MROUND(C2-1/288,1/48)

Explain:
1/288 ~ 5 minutes. This is subtracted to cause numbers in the 15-19 range to
get knocked down to the 10-14 range (similarly is knock 45-49 down to 40-44).

1/48 = 30 minutes. The formula then round the difference from previous
operation to the nearest 30 minute mark.

 

[puiuluipui]

Hi Luke, your formula gives me "#name?"
What am i doing wrong?
Thanks!

 

[Peo Sjoblom]

Look up Analysis ToolPak functions in help and install them and it will work

This should work using a non ATF function

=ROUND((C2-1/288)/TIME(,30,),)*TIME(,30,)

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Doh! Should of course be a non ATP function

--


Regards,


Peo Sjoblom

 

[puiuluipui]

Thanks allot!

Assuming you've already calculated the amount of overtime in cell C2
(something like =a2-a1-"8:00")

The formula you need for desired result is:
=MROUND(C2-1/288,1/48)

Explain:
1/288 ~ 5 minutes. This is subtracted to cause numbers in the 15-19 range to
get knocked down to the 10-14 range (similarly is knock 45-49 down to 40-44).

1/48 = 30 minutes. The formula then round the difference from previous
operation to the nearest 30 minute mark.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[puiuluipui]

I't working with both ideeas.
Thanks allot!

Look up Analysis ToolPak functions in help and install them and it will work

This should work using a non ATF function

=ROUND((C2-1/288)/TIME(,30,),)*TIME(,30,)

--


Regards,


Peo Sjoblom





.

 

[puiuluipui]

One more question. What if i will need to do the same thing but to round with
15 minutes. Or with 5 minutes.
How the code will look like?
Thanks!

 

[Peo Sjoblom]

It all depends on the rules, if you just wanted a straight rounding to the
nearest 15th minute or 5th minute you can use

=ROUND(C2/TIME(,15,),)*TIME(,15,)

=ROUND(C2/TIME(,5,),)*TIME(,5,)


but in the other example you wanted to round 20-30 to 30 and 0-19 to 0 so it
all depends if you have any other restrictions

--


Regards,


Peo Sjoblom

 

[puiuluipui]

Hi, i need the code to calculate exactly like your first one :
=ROUND((C2-1/288)/TIME(,30,),)*TIME(,30,)
I need the code to calculate from 30 to 30 minutes, but to rundup to 15
minutes till 30.. and the same for 5 minutes.

Can this be done?
Thanks!