Time rounding help

[Sanch]

I am working on a time sheet that I need to tweak slightly, currently
I have a time in and time out log:

In: 6:58
Out 17:36

I am trying to round these numbers to the nearest hour 6:58 to 7am,
however 17:36 needs to round down to 17:00. The formula I am curently
using is: =TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) The M references
the rounding #. It is currently set at 60.

Scott

 

[Bob Phillips]

=ROUND(A1*24,0)/24

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Sanch]

This works on the first portion of the formula, but I need to orund
down the second #, Do you know how I can accomplish this?

I need to turn 17:36 into 17:00.

Do you know why when I use the following formula
=TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) it does not orund to the
nearest zero. 17:15 become 17:00, but 6:58 become 6:00.

Thanks,


Scott

 

[Bob Phillips]

Why, you originally said ROUND not ROUNDDOWN.

=ROUNDDOWN(A1*24,0)/24

or

=FLOOR(A1,1/24)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)