How to round down to nearest 5 minutes, time calc?

  • Thread starter Thread starter StargateFanFromWork
  • Start date Start date
S

StargateFanFromWork

Just when I think this spreadsheet is working, I find something slightly
wrong <g>.

There are 2 "totals" cells. The first totals cell shows total exact time,
the other shows it rounded down. The complex rounding down formula I got
several weeks ago does the rounding bit but doesn't show total time
correctly. I didn't have enough hours until now for this to show up. After
24 hours, it subtracts 24 and just shows time as if it were a clock time
(i.e., rather than 25 hours, it shows 01h00, as if it were 1 o'clock a.m.
instead of a sum of 25 hours). Anywho, the initial rounding formula doesn't
let [h]:mm work, but the regular rounding one I recvd yesterday in the group
allows [h]:mm to work perfectly but isn't rounding down, just rounding in
general. In the example below, it is rounding _UP_ 2 minutes.

i.e., the real total in the test hours I plugged in shows these results:
72h58m
while the cell that rounds shows:
73h00m

As this is talking about overtime, this is not good. I need it to round
down but hopefully just to closest five minutes. I don't want to get paid
for 2 extra minutes because that would freak out the overtime people
eventually <g>, but rounding down to 72h55m, for example, would be perfectly
fine. I'd lose out on 3 minutes of overtime but this way, no problems with
my employer.

Is there a way to get the formula, which is in this particular case is:
=ROUND(SUM(J2:J31)*96,0)/96
to round DOWN to nearest 5 minutes?
Hopefully the cell format of [h]:mm will still work, too.

Thanks so much! Eventually this darn sheet will work and I will be able to
share it with the rest of the floor here! :oD
 
Your formula rounds to the nearest 15 minutes, but try this:

=ROUNDDOWN(SUM(J2:J31)*96,0)/96

format the cell as

[h]:mm
 
=ROUNDDOWN(A1*288,0)/288

I don't think the 96 was right for 5 minutes anyway. More like 15.

--
Jim
| Just when I think this spreadsheet is working, I find something slightly
| wrong <g>.
|
| There are 2 "totals" cells. The first totals cell shows total exact time,
| the other shows it rounded down. The complex rounding down formula I got
| several weeks ago does the rounding bit but doesn't show total time
| correctly. I didn't have enough hours until now for this to show up.
After
| 24 hours, it subtracts 24 and just shows time as if it were a clock time
| (i.e., rather than 25 hours, it shows 01h00, as if it were 1 o'clock a.m.
| instead of a sum of 25 hours). Anywho, the initial rounding formula
doesn't
| let [h]:mm work, but the regular rounding one I recvd yesterday in the
group
| allows [h]:mm to work perfectly but isn't rounding down, just rounding in
| general. In the example below, it is rounding _UP_ 2 minutes.
|
| i.e., the real total in the test hours I plugged in shows these results:
| 72h58m
| while the cell that rounds shows:
| 73h00m
|
| As this is talking about overtime, this is not good. I need it to round
| down but hopefully just to closest five minutes. I don't want to get paid
| for 2 extra minutes because that would freak out the overtime people
| eventually <g>, but rounding down to 72h55m, for example, would be
perfectly
| fine. I'd lose out on 3 minutes of overtime but this way, no problems
with
| my employer.
|
| Is there a way to get the formula, which is in this particular case is:
| =ROUND(SUM(J2:J31)*96,0)/96
| to round DOWN to nearest 5 minutes?
| Hopefully the cell format of [h]:mm will still work, too.
|
| Thanks so much! Eventually this darn sheet will work and I will be able
to
| share it with the rest of the floor here! :oD
|
|
 
Your formula rounds to 15 mins, your subject says to 5 mins.

Assuming you want 15 mins, use

=ROUNDDOWN(SUM(J2:J31)*96,0)/96

If you want 5 mins, use

=ROUNDDOWN(SUM(J2:J31)*288,0)/288

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Hi! I don't question the 288 approach, yet I would like to have something
easier to manipulate. So I thought of the following approach which I believe
to be quite easy to adjust to other situations as well. See if it helps and
let me know!

Apply this to the 58 [put your cell reference in the place of 58] and you
round it down to 5 min... = INT(58/5)*5 =>55

This is only for ROUNDING DOWN - see further down for a more generic approach.

Explanation:
First of all, the number to be rounded must be in the same measuring unit as
the rounding unit. Yours is 5 "minutes" so the number to be rounded must be
in minutes. i.e. 72h 58 min = (72*60)+58=4378 OR you should apply rounding to
the 58 part only (and take action if you produce a 60 by rounding up, but
this is not your case). Converting the whole number is a no-exceptions
approach.

Then apply this formula : INT(value_to_be_rounded / rounding_value) to get
the integer part and check the remainder using the MOD function. If a
remainder exists and you round down you have to ignore it. If you round up
you have to make it a whole unit.

In your case, rounding down, this leaves us with =INT(B1/5)*5

For both hour and minute manipulation use the following for ROUNDING DOWN

A1 = 72h
B1 = 58mins
A2 : INT( INT((A1*60+B1)/5)*5 /60) ===> rounded hours = 72
B2 : MOD(INT((A1*60+B1)/5)*5;60) ===> remaining mins = 55

***And last but not least this could be coded in a function and account for
either rounding up or down, according to passed on parameters:

p1n = parameter1, numeric, number to be rounded
p2n = parameter2, numeric, rounding number
p3b = parameter3, boolean, TRUE=round up, FALSE=round down

result=INT(p1n/p2n)*p2n+IF(AND(MOD(p1n;p2n)>0;p3b=TRUE);p2n;0)

HTH
 
I finally understood what the 288 is when I noticed that you only have one
field formatted as [h]:mm, which is actually in DAYS formatted as
hours/minutes. Therefore 288=1440/5 and it is used to convert days to 5
minute intervals.

So, in order to use my approach you have to multiply your single-cell-time
with 1440 to convert it to minutes and then apply my formulas. That results
to the answers by Bob and Jim.
 
To be clearer, you can always multiply by TIME(0,5,0) if it helps. This is
the same as dividing by 288.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top