MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

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

• Follow

```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
```
 0

```>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" <TeriS@discussions.microsoft.com> wrote in message
> 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

```
 0

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

>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

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
```
 0

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

>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

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".
```
 0

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

>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

Check out the time sheet here:

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

Look at Wally's other workbooks too.
```
 0

4 Replies
773 Views

8/6/2012 2:12:20 PM