Rounding off TIME
 

I need the following formula to round off to the nearest minute. Currently
it just drops the seconds off without rounding the minute off.

+b11-b10

where...
B10 is currently =TIME(HOUR(B10),MINUTE(B10)+$D$43/$D$40*$D$44/100,0)
B11 is currently =TIME(HOUR(B11),MINUTE(B11)+$D$43/$D$40*$D$44/100,0)

D43 is currently 189.52
D40 is currently 15.47
D44 is currently 100

I can't seem to find the proper cell format to show the minutes rounded off.
These cells are from a time sheet calculating how often a new batch can be
run.
I hope this is enough info.
Thanks,
Frank.

Use the Mround function. Mround will round to the nearest multiple of the number
you specify. Just enter one minute as the multiple. Excel stores times as a
fraction of a day, so one minute is 1/24/60. Therefore, use:

=mround(b11-b10,1/24/60)

If you prefer to see a time format in the formula, you can use:

=mround(b11-b10,"0:01:00")

--
Regards,
Fred


"FJ Shepley & JM Pfohl" wrote in message
...
I need the following formula to round off to the nearest minute. Currently
it just drops the seconds off without rounding the minute off.

+b11-b10

where...
B10 is currently =TIME(HOUR(B10),MINUTE(B10)+$D$43/$D$40*$D$44/100,0)
B11 is currently =TIME(HOUR(B11),MINUTE(B11)+$D$43/$D$40*$D$44/100,0)

D43 is currently 189.52
D40 is currently 15.47
D44 is currently 100

I can't seem to find the proper cell format to show the minutes rounded off.
These cells are from a time sheet calculating how often a new batch can be
run.
I hope this is enough info.
Thanks,
Frank.

One way:

=TIME(HOUR(B10),MINUTE(B10),$D$43/$D$40*$D$44*60/100)

In article ,
"FJ Shepley & JM Pfohl" wrote:

I need the following formula to round off to the nearest minute. Currently
it just drops the seconds off without rounding the minute off.

+b11-b10

where...
B10 is currently =TIME(HOUR(B10),MINUTE(B10)+$D$43/$D$40*$D$44/100,0)
B11 is currently =TIME(HOUR(B11),MINUTE(B11)+$D$43/$D$40*$D$44/100,0)

D43 is currently 189.52
D40 is currently 15.47
D44 is currently 100

I can't seem to find the proper cell format to show the minutes rounded off.
These cells are from a time sheet calculating how often a new batch can be
run.
I hope this is enough info.
Thanks,
Frank.