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

--
Regards,
Tom Ogilvy


"StargateFanFromWork" wrote in message
...
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

=ROUNDDOWN(A1*288,0)/288

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

--
Jim
"StargateFanFromWork" wrote in message
...
| 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)

"StargateFanFromWork" wrote in message
...
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