Rounding hh:mm to nearest 15, 30, 45 minutes

[TeriS]

I, too, am setting up a timesheet. It is a very simple timesheet but I can't
seem to figure out this (what should be) simple function. My question is:

Time In Time Out Total Minutes worked (These are usually 45 min
classes)
9:00 AM 9:45 AM 0:45
9:30 AM 10:15 AM 0:45
9:00 AM 9:35 AM 0:35
Total time worked 1 hr. 20 min. This is the time I need rounded
to the nearest 1/4 hour. I can do it in the same cell or a cell directly
below this one. Whichever is easiest (I am NOT Excel proficient).

TIA,
Teri

[T. Valko]

>Total time worked...1 hr. 20 min

I think your total time should be 2:05. Rounded to the nearest quarter hour
would be 2:00.

Try this...

=ROUND(SUM(C2:C4)*96,0)/96

--
Biff
Microsoft Excel MVP

"TeriS" <Te...@discussions.microsoft.com> wrote in message
news:E47F3618-0D1E-4881...@microsoft.com...

[Ron Rosenfeld]

On Fri, 15 Jan 2010 17:31:19 -0800, TeriS <Te...@discussions.microsoft.com>
wrote:

I don't understand how you got 1 hr 20 min from those three working times. But,
in general:

=ROUND(YourSumFormula/TIME(0,15,0),0)*TIME(0,15,0)

or, if you have Excel 2007+ or the Analysis Tool Pak installed:

=MROUND(YourSumFormula,TIME(0,15,0))

--ron

[CellShocked]

On Fri, 15 Jan 2010 17:31:19 -0800, TeriS
<Te...@discussions.microsoft.com> wrote:

If you want to tally only 15 minute increments, you can (should) only
use 15 minute increments.

The "9:35" entry is invalid in such a policy. If you FORCE all entries
to be at 15 minute increments only, you no longer need to round anything
off. You can do that by way of "data validation".

[WallyWallWhackr]

On Fri, 15 Jan 2010 17:31:19 -0800, TeriS
<Te...@discussions.microsoft.com> wrote:

Check out the time sheet here:

http://office.microsoft.com/en-us/templates/TC300083091033.aspx?pid=CT101172771033

Look at Wally's other workbooks too.

[mistyda...@gmail.com]

You can put this below/to the right (or in any empty cell really) of the value you want rounded to the nearest 15 minute mark and replace the "A1" cell reference in the formula with a reference to the cell you are wanting to round. =mround(A1, 1/96)