how do I round to nearest quarter hour

[Firstime]

I need to round a time to the nearest quarter hour and have it show in quarters. My formula figures out the hours worked. If the total is 8.36, then it needs to show 8.75. It needs to round up or down to nearest quater hour. How can I do this??

 

[onlyadrafter]

Hello,

If the answer is 8.36 should this be rounded to 8.5 not 8.75?
Can you elaborate?

 

[Pesky_Weasel]

Firsttime:

This broke my brain for a little bit, hope it works for you.
Enter this where the timevalue (Format hh:mm) you want to round up is in ColA (A3 in this case)

=(VALUE(A3/0.0104166666666667)+(1-(VALUE(A3/0.0104166666666667)-ROUNDDOWN(VALUE(A3/0.0104166666666667),0))))*0.0104166666666667

This will round up every value that exceed the 15minute mark to the next 15 mins.


regards
Weasel

 

[Mark W.]

I need to round a time to the nearest quarter hour and have it show in quarters. My formula figures out the hours worked. If the total is 8.36, then it needs to show 8.75. It needs to round up or down to nearest quater hour. How can I do this??

I believe onlyadrafter has a point! Shouldn't 8.36 be rounded to 8.5? That's the results if you use...

=CEILING(8.36,0.25)

 

[gaynard_nelson]

I need to round a time to the nearest quarter hour and have it show in quarters. My formula figures out the hours worked. If the total is 8.36, then it needs to show 8.75. It needs to round up or down to nearest quater hour. How can I do this??

I understand the 8.36 is hours and minutes. If that is correct try this

A1 B1 C1
8.36 0.75 8.75

A1 8 Hours
A1 0.36 Minutes
B1 Minute part of A1 =ROUND(A1-INT(A1),2)*
*Must round to avoid 15th place problem with 30 minutes

C1 Converts minute portion of A1 to quarter Hours and adds whole hours

=INT(A1)+IF(B1=0,0,IF(B1<=0.15,0.25,IF(B1<=0.3,0.5,IF(B1<=0.45,0.75,1))))

 

[Mark W.]

I understand the 8.36 is hours and minutes...

If 8.36 was in the form of h.mm wouldn't the nearest quarter hour be 8.45 rather than 8.75?

 

[just_jon]

I think we're a bit confused over whether the OP wants a return in h:mm or decimal form; reading his question and expected result it looks like the 8:36 is h:mm which would round up to 8:45 but is 8.75 decimal.

 

[gaynard_nelson]

I understand the 8.36 is hours and minutes...

If 8.36 was in the form of h.mm wouldn't the nearest quarter hour be 8.45 rather than 8.75?

I think what he is saying (I could be wrong) is 8.36 is 8 hours and 36 minutes. It looks to me like he is trying to convert that to hours and fractions of an hours ( for payroll purposes) in quarter hours increments so 8 hours and 36 minutes would round up to 8 and 3/4 of an hour (0.75)

 

[bosco_yip]

Hi Nelson,

I agreed with your idea, the OP is saying 8.36 in [h].mm and to convert to hours and quarter of an hour, so he wanted in 8.75

The roundup fomula :
=INT(A1)+CEILING(((A1-INT(A1))*100),15)/60

The rounddown fomula :
=INT(A1)+FLOOR(((A1-INT(A1))*100),15)/60

Regards

 

[Pesky_Weasel]

Dear All,

Yes, it is a little confusing as to what the Op wants.

Here was my take.

1) totally missed the round down part - just looked at the numbers and assumed that if you go past the 1/4 interval it gets rounded up (as should be the case in any payroll situation)

2) final answer should be in hours as a fraction

The solution I provided originally will give a finish time that is ROUNDED UP to the nearest 15 minutes. The OP can then use that timevlaue and subtract the start time to give an hours worked result
The formula:
=TEXT(HOUR(A4) & "."& (100/(60/MINUTE(A4))),"00.00")

Will convert the hours worked into a decimal number.

Regards
Weasel