Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
If someone was to work 7:00am to 8:15am my result is 1.25 hrs. I am trying to
round up which I can do easily however if the Total time is Less than 1.25 I would like for it to round down to 1hr. How do I put that into a formula. Here is how my formula looks IN OUT In out Hour Minute value Hour minute value total Hrs Rounded 7:00:00am 8:15:00am 7 - 7.0 8 15 8.25 1.25 2.0 now if the total hr is 1.24 I need that to have a rounded value of 1.0 I hope I explained myself clear enout Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
Darts wrote:
If someone was to work 7:00am to 8:15am my result is 1.25 hrs. I am trying to round up which I can do easily however if the Total time is Less than 1.25 I would like for it to round down to 1hr. How do I put that into a formula. if the total hr is 1.24 I need that to have a rounded value of 1.0 I hope I explained myself clear enout Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
I can't quite see exactly what you're trying to do but in general if you want to round down a positive value to the previous 0.25 =FLOOR(A1,0.25) if your value is calculated by a formula then just enclose your formula in the FLOOR function thus =FLOOR(formula,0.25) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525425 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
daddylonglegs wrote:
I can't quite see exactly what you're trying to do but in general if yo want to round down a positive value to the previous 0.25 =FLOOR(A1,0.25) if your value is calculated by a formula then just enclose your formul in the FLOOR function thus =FLOOR(formula,0.25 If the value is 1.25 I want that to round up to 2.0 If the value is <1.25 I want that to round down to 1.0 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
So does that apply to other numbers, what happens to 3.3 or 5.4? Perhaps try =ROUND(A1+0.25,0) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525425 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
=INT(A1+0.75)
..25<=A1<1.25 will result in 1 1.25<=A1<2.25 will result in 2 2.25<=A1<3.25 will result in 3 etc. "Darts" wrote: daddylonglegs wrote: I can't quite see exactly what you're trying to do but in general if yo want to round down a positive value to the previous 0.25 =FLOOR(A1,0.25) if your value is calculated by a formula then just enclose your formul in the FLOOR function thus =FLOOR(formula,0.25 If the value is 1.25 I want that to round up to 2.0 If the value is <1.25 I want that to round down to 1.0 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
daddylonglegs wrote:
So does that apply to other numbers, what happens to 3.3 or 5.4? Perhaps try =ROUND(A1+0.25,0 3.3 would round to 4 since .3 equates to 15 minutes or greater in time 5.4 would round to 6 sinc .4 equates to 15 minutes or greater I am not sure but .23 is equivalent to 14 minutes. I am not very good at explaining things. If the total hr adds up to 1.24 then I would like that number rounded to 1 If it is 1.25 rounded to 2. I hope this helps so you can help me!!!! I'm pretty new at this and all of you sound like einsteins!!!!!!!!!!!!!!! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
Sounds like =ROUND(A1+0.25,0) would work for you then, or perhaps Sloth's (shorter) suggestion =INT(A1+0.75) I believe these would do the same thing -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525425 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
Sloth wrote:
=INT(A1+0.75) .25<=A1<1.25 will result in 1 1.25<=A1<2.25 will result in 2 2.25<=A1<3.25 will result in 3 etc. I can't quite see exactly what you're trying to do but in general if yo want to round down a positive value to the previous 0.25 [quoted text clipped - 8 lines] If the value is <1.25 I want that to round down to 1.0 Oh My God that worked!!!!! Thank You Thank You Thank You What does INT mean ? How did you figure this out? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
INT takes the integer portion of a number. So INT(1.23456)=1, and
INT(22.99999)=22. Your problem was similar to a rounding trick I learned in a programming class a long time ago. The problem in that class was to use the INT function to round a number; the solution was INT(A1+0.5). You are actually wanted a skewed rounding, so I figured the solution had to be similar. That's kind of how I figured it out. Sorry if I confused you, I'm not real good at explaining my thought processes. "Darts" wrote: Sloth wrote: =INT(A1+0.75) .25<=A1<1.25 will result in 1 1.25<=A1<2.25 will result in 2 2.25<=A1<3.25 will result in 3 etc. I can't quite see exactly what you're trying to do but in general if yo want to round down a positive value to the previous 0.25 [quoted text clipped - 8 lines] If the value is <1.25 I want that to round down to 1.0 Oh My God that worked!!!!! Thank You Thank You Thank You What does INT mean ? How did you figure this out? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
daddylonglegs wrote:
Sounds like =ROUND(A1+0.25,0) would work for you then, or perhaps Sloth's (shorter suggestion =INT(A1+0.75) I believe these would do the same thin Thank You !! Your version does work as well. When doing formulas the , represents what? and what does the 0 after the , mean? Is there a site so I can see or try to understand. Once I see a formula I can do it but I do not understand. thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200603/1 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Time
I suggest looking at Excel Help as a starting point. The ROUND function needs 2 arguments, these are separated by the comma. The first argument is the value to be rounded, the second argument represents the number of decimal places to round to. Zero means the nearest integer. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525425 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet to calculate 2 different columns | Excel Worksheet Functions | |||
Excel Time Sheet - Rounding To Quarter Hours | Excel Worksheet Functions | |||
Rounding Time? | Charts and Charting in Excel | |||
rounding time question | Excel Worksheet Functions | |||
Rounding time in excel (2003) | Excel Discussion (Misc queries) |