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

[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! D

 

[Tom Ogilvy]

Your formula rounds to the nearest 15 minutes, but try this:

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

format the cell as

[h]:mm

 

[Jim Rech]

=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! D
|
|

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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.

 

[Bob Phillips]

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)