Rounding Time.

[GEM]

How can I round time UP to the nearest 5 min. I need to have the report in
numbers divisible by 5 and the time should always go up. 1:30, 1:35, 1:40,
1:45.

For example,

1:41 round it to 1:45
1:43 round it to 1:45
1:46 round it to 1:50
1:48 round it to 1:50
1:40 is kept at 1:40
1:50 is kept at 1:50

 

[David Biddulph]

=CEILING(A2,TIME(,5,))

 

[GEM]

Thank you!!!!!!!!!! Just for general knowledge... and to round that number
down???

 

[GEM]

I'm having problems. On some cells that I entered 1:00 it's rounding it up to
1:05. Is there something wrong?? There's no decimals because I entered the
time 1:00 it's not giving it out as a formula.

 

[David Biddulph]

I would recommend to you the feature of Excel help for a function (in this
case CEILING) that says "See Also".. It will show you the function that has
the opposite effect to CEILING, and it will also show you a number of
similar functions which may be useful to you in future.

 

[David Biddulph]

Which version of Excel? On Excel 2003, an input of 1:00 is giving 1:00 as
the output of my formula.

You say "There's no decimals because I entered the time 1:00 ...", but in
fact 1:00 does involve decimals (or more strictly non-integer binary) as
Excel times are stored as a fraction of a day. If you reformat 1:00 from
time to General you'll see that it comes out as 0.0416666666666667, and
Excel works to a precision of 15 significant figures so formatting as number
with 20 decimal places will show you 0.04166666666666670000 as its best
approximation to 1/24 of a day. Despite that, as I said earlier, Excel 2003
gives the 1:00 result from my formula, but I know that there are some cases
where Excel 2007 behaves differently (and not always "better") in how it
treats rounding errors.

If the worst comes to the worst, there might be a solution, if you wish to
work in whole minutes, by dividing by TIME(,1,), then rounding to the
nearest integer, then do a CEILING(...,5), then multiplying back by
TIME(,1,). A bit tortuous [=CEILING(ROUND(A10/TIME(,1,),0),5)*TIME(,1,)],
but ought to solve the problem. Another, perhaps cleaner, way of doing
this, would be =CEILING(ROUND(A10*24*60,0),5)/24/60

 

[GEM]

Thank you!! It worked with this function =CEILING(ROUND(I23*24*60,0),5)/24/60

What was I doing wrong?? With the other one I would get 1:05, now it's
correct and I get 1:00.

Which version of Excel? On Excel 2003, an input of 1:00 is giving 1:00 as
the output of my formula.

You say "There's no decimals because I entered the time 1:00 ...", but in
fact 1:00 does involve decimals (or more strictly non-integer binary) as
Excel times are stored as a fraction of a day. If you reformat 1:00 from
time to General you'll see that it comes out as 0.0416666666666667, and
Excel works to a precision of 15 significant figures so formatting as number
with 20 decimal places will show you 0.04166666666666670000 as its best
approximation to 1/24 of a day. Despite that, as I said earlier, Excel 2003
gives the 1:00 result from my formula, but I know that there are some cases
where Excel 2007 behaves differently (and not always "better") in how it
treats rounding errors.

If the worst comes to the worst, there might be a solution, if you wish to
work in whole minutes, by dividing by TIME(,1,), then rounding to the
nearest integer, then do a CEILING(...,5), then multiplying back by
TIME(,1,). A bit tortuous [=CEILING(ROUND(A10/TIME(,1,),0),5)*TIME(,1,)],
but ought to solve the problem. Another, perhaps cleaner, way of doing
this, would be =CEILING(ROUND(A10*24*60,0),5)/24/60
--
David Biddulph

I'm having problems. On some cells that I entered 1:00 it's rounding it up
to
1:05. Is there something wrong?? There's no decimals because I entered the
time 1:00 it's not giving it out as a formula.

 

[David Biddulph]

I think you missed my question:
"Which version of Excel?"

Also, if you reformat the cell containing 1:00 to general, what number do
you see (in the cell, and particularly in the formula bar)?
--
David Biddulph

Thank you!! It worked with this function
=CEILING(ROUND(I23*24*60,0),5)/24/60

What was I doing wrong?? With the other one I would get 1:05, now it's
correct and I get 1:00.

Which version of Excel? On Excel 2003, an input of 1:00 is giving 1:00
as
the output of my formula.

You say "There's no decimals because I entered the time 1:00 ...", but in
fact 1:00 does involve decimals (or more strictly non-integer binary) as
Excel times are stored as a fraction of a day. If you reformat 1:00 from
time to General you'll see that it comes out as 0.0416666666666667, and
Excel works to a precision of 15 significant figures so formatting as
number
with 20 decimal places will show you 0.04166666666666670000 as its best
approximation to 1/24 of a day. Despite that, as I said earlier, Excel
2003
gives the 1:00 result from my formula, but I know that there are some
cases
where Excel 2007 behaves differently (and not always "better") in how it
treats rounding errors.

If the worst comes to the worst, there might be a solution, if you wish
to
work in whole minutes, by dividing by TIME(,1,), then rounding to the
nearest integer, then do a CEILING(...,5), then multiplying back by
TIME(,1,). A bit tortuous
[=CEILING(ROUND(A10/TIME(,1,),0),5)*TIME(,1,)],
but ought to solve the problem. Another, perhaps cleaner, way of doing
this, would be =CEILING(ROUND(A10*24*60,0),5)/24/60
--
David Biddulph

I'm having problems. On some cells that I entered 1:00 it's rounding it
up
to
1:05. Is there something wrong?? There's no decimals because I entered
the
time 1:00 it's not giving it out as a formula.

:

Thank you!!!!!!!!!! Just for general knowledge... and to round that
number
down???

:

=CEILING(A2,TIME(,5,))
--
David Biddulph

How can I round time UP to the nearest 5 min. I need to have the
report in
numbers divisible by 5 and the time should always go up. 1:30,
1:35,
1:40,
1:45.

For example,

1:41 round it to 1:45
1:43 round it to 1:45
1:46 round it to 1:50
1:48 round it to 1:50
1:40 is kept at 1:40
1:50 is kept at 1:50