Rounding Time

[Darts]

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

[Darts]

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

[daddylonglegs]

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)

[Darts]

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

[daddylonglegs]

So does that apply to other numbers, what happens to 3.3 or 5.4?

Perhaps try

=ROUND(A1+0.25,0)

[Sloth]

=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
>

[Darts]

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!!!!!!!!!!!!!!!

[daddylonglegs]

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

[Darts]

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?

[Sloth]

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?
>

[Darts via OfficeKB.com]

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

[daddylonglegs]

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.