Overtime

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

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!
 
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.
 
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
 
Thanks allot!

Luke M said:
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!*
Click to expand...
 
I't working with both ideeas.
Thanks allot!

Peo Sjoblom said:
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





.
Click to expand...
 
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!
 
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
 
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!
 
Back
Top