Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
=CEILING(A2,TIME(,5,))
-- David Biddulph "GEM" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
Thank you!!!!!!!!!! Just for general knowledge... and to round that number
down??? "David Biddulph" wrote: =CEILING(A2,TIME(,5,)) -- David Biddulph "GEM" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
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. "GEM" wrote: Thank you!!!!!!!!!! Just for general knowledge... and to round that number down??? "David Biddulph" wrote: =CEILING(A2,TIME(,5,)) -- David Biddulph "GEM" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
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 "GEM" wrote in message ... Thank you!!!!!!!!!! Just for general knowledge... and to round that number down??? "David Biddulph" wrote: =CEILING(A2,TIME(,5,)) -- David Biddulph "GEM" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
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 "GEM" wrote in message ... 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. "GEM" wrote: Thank you!!!!!!!!!! Just for general knowledge... and to round that number down??? "David Biddulph" wrote: =CEILING(A2,TIME(,5,)) -- David Biddulph "GEM" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
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. "David Biddulph" wrote: 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 "GEM" wrote in message ... 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. "GEM" wrote: Thank you!!!!!!!!!! Just for general knowledge... and to round that number down??? "David Biddulph" wrote: =CEILING(A2,TIME(,5,)) -- David Biddulph "GEM" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Time.
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 "GEM" wrote in message ... 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. "David Biddulph" wrote: 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 "GEM" wrote in message ... 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. "GEM" wrote: Thank you!!!!!!!!!! Just for general knowledge... and to round that number down??? "David Biddulph" wrote: =CEILING(A2,TIME(,5,)) -- David Biddulph "GEM" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding time | Excel Worksheet Functions | |||
rounding time | Excel Worksheet Functions | |||
Time rounding help | Excel Discussion (Misc queries) | |||
Rounding off TIME | Excel Discussion (Misc queries) | |||
Rounding of Time | Excel Worksheet Functions |