Good afternoon --

I'm having problems setting up a time sheet template, which requires
rounding the employee's time to the nearest quarter hour. Here's what I've
got:

A B
1 7:12 AM =MROUND(A1,0.010417)

The 0.010417 number I believe to be the decimal value for 15 minutes. At
any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM like
I want.

What am I doing wrong?

Thanks in advance.

You may need to recheck your typing, or your initial value.

To be accurate you should use 1/96 instead of 0.010417, as the latter has
some rounding errors from the true value of 0.0104166666666... (with the 6s
continuing to infinity), but the 0.010417 will give 7:15 AM if A1 is simply
7:12 AM. Why don't you reread your formula, and see whether you've used
=MROUND(A1,0.01047) instead of =MROUND(A1,0.010417)?

The other thing which might exaggerate the rounding error if you have got
0.010417 would be if A1 isn't just a time but contains date and time, merely
formatted as time.
--
David Biddulph

"pdberger" wrote in message
...
Good afternoon --

I'm having problems setting up a time sheet template, which requires
rounding the employee's time to the nearest quarter hour. Here's what
I've
got:

A B
1 7:12 AM =MROUND(A1,0.010417)

The 0.010417 number I believe to be the decimal value for 15 minutes. At
any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM
like
I want.

What am I doing wrong?

Thanks in advance.

You may need to recheck your typing, or your initial value.

To be accurate you should use 1/96 instead of 0.010417, as the latter has
some rounding errors from the true value of 0.0104166666666... (with the
6s continuing to infinity)

Or, instead of having to calculate divisors, you can let Excel do all the
work...

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

Rick

When I tried this I got only "#NAME?" I also want to round UP to the nearest
15 minutes. I usually have a column C with Minutes that I enter by hand and
the next column D would be MINUTES rounded up to the nearest 15 minutes.
Please advise how to do this? Thanks. Amy

"Rick Rothstein (MVP - VB)" wrote:

You may need to recheck your typing, or your initial value.

To be accurate you should use 1/96 instead of 0.010417, as the latter has
some rounding errors from the true value of 0.0104166666666... (with the
6s continuing to infinity)

Or, instead of having to calculate divisors, you can let Excel do all the
work...

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

Rick

Assuming you enter the times like this:

7:22 = 7 minutes 22 seconds

Try this:

=ROUNDUP(A1/(1/96),0)*(1/96)

Format as mm:ss

--
Biff
Microsoft Excel MVP


"Amy" wrote in message
...
When I tried this I got only "#NAME?" I also want to round UP to the
nearest
15 minutes. I usually have a column C with Minutes that I enter by hand
and
the next column D would be MINUTES rounded up to the nearest 15 minutes.
Please advise how to do this? Thanks. Amy

"Rick Rothstein (MVP - VB)" wrote:

You may need to recheck your typing, or your initial value.

To be accurate you should use 1/96 instead of 0.010417, as the latter
has
some rounding errors from the true value of 0.0104166666666... (with
the
6s continuing to infinity)

Or, instead of having to calculate divisors, you can let Excel do all the
work...

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

Rick

Another one:

=CEILING(A1,1/96)

Format as mm:ss

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assuming you enter the times like this:

7:22 = 7 minutes 22 seconds

Try this:

=ROUNDUP(A1/(1/96),0)*(1/96)

Format as mm:ss

--
Biff
Microsoft Excel MVP


"Amy" wrote in message
...
When I tried this I got only "#NAME?" I also want to round UP to the
nearest
15 minutes. I usually have a column C with Minutes that I enter by hand
and
the next column D would be MINUTES rounded up to the nearest 15 minutes.
Please advise how to do this? Thanks. Amy

"Rick Rothstein (MVP - VB)" wrote:

You may need to recheck your typing, or your initial value.

To be accurate you should use 1/96 instead of 0.010417, as the latter
has
some rounding errors from the true value of 0.0104166666666... (with
the
6s continuing to infinity)

Or, instead of having to calculate divisors, you can let Excel do all
the
work...

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

Rick

So when you got a #NAME response from the MROUND function, did you look up
MROUND in Excel help?

If you want to round up, you probably want to look at the CEILING function.
--
David Biddulph

"Amy" wrote in message
...
When I tried this I got only "#NAME?" I also want to round UP to the
nearest
15 minutes. I usually have a column C with Minutes that I enter by hand
and
the next column D would be MINUTES rounded up to the nearest 15 minutes.
Please advise how to do this? Thanks. Amy

"Rick Rothstein (MVP - VB)" wrote:

You may need to recheck your typing, or your initial value.

To be accurate you should use 1/96 instead of 0.010417, as the latter
has
some rounding errors from the true value of 0.0104166666666... (with
the
6s continuing to infinity)

Or, instead of having to calculate divisors, you can let Excel do all the
work...

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

Rick

When I tried this I got only "#NAME?"

I'm not sure why you would have gotten that error... the formula I posted
works fine in XL2003 and XL2007.

I also want to round UP to the nearest 15 minutes.

Which you realize you did not say in your original posting, right? This
formula should do what you are now asking for...

=CEILING(A1,TIME(0,15,0))


Rick

Analysis ToolPak, Rick?
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
When I tried this I got only "#NAME?"

I'm not sure why you would have gotten that error... the formula I posted
works fine in XL2003 and XL2007.
....

=ROUND(A1*96,0)/96
or
=MROUND(A1,1/96)

format as h:mm


"pdberger" wrote:

Good afternoon --

I'm having problems setting up a time sheet template, which requires
rounding the employee's time to the nearest quarter hour. Here's what I've
got:

A B
1 7:12 AM =MROUND(A1,0.010417)

The 0.010417 number I believe to be the decimal value for 15 minutes. At
any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM like
I want.

What am I doing wrong?

Thanks in advance.

=IF(A1-FLOOR(A1,0.01041666667)CEILING(A1,0.01041666667)-A1,CEILING(A1,0.01041666667),FLOOR(A1,0.0104166666 7))

The above formula will round to the NEAREST quarter hour.

If you only want to round up, use
=ceiling(a1,0.01041666667).
If you only want to round down, use
=floor(a1,0.01041666667)
--
JNW


"pdberger" wrote:

Good afternoon --

I'm having problems setting up a time sheet template, which requires
rounding the employee's time to the nearest quarter hour. Here's what I've
got:

A B
1 7:12 AM =MROUND(A1,0.010417)

The 0.010417 number I believe to be the decimal value for 15 minutes. At
any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM like
I want.

What am I doing wrong?

Thanks in advance.