Solved Calculating time worked then rounding to nearest quarter hou

April 28, 2013 at 21:21:21
Specs: Windows 7
I need a formula to calculate hours worked and then rounding up or down to the nearest quarter hour. I have start time (A1) and end time (B1) in time format then total hours worked are calculated in(C1) then I physcally round up or down actual hrs worked in (D1) as I pay in quarter hour increments.

Example;
A1-Start B1-End C1-Hrs D1-Rounded

8:42 AM 2:45 PM 6:03 6.00
8:50 AM 1:20 PM 4:30 4.50
8:45 AM 1:16 PM 4:31 4.50


See More: Calculating time worked then rounding to nearest quarter hou

Report •


#1
April 29, 2013 at 10:04:17
✔ Best Answer
Try this =MROUND() formula:

=MROUND((B1-A1),"0:15")*24

MIKE

http://www.skeptic.com/


Report •

#2
April 29, 2013 at 10:32:30
Hi Mike, thanks for the quick response. Unfortunately that formula isn't working. Below is what it's coming out as,. Is it possible I should have the cell fomats as something different?

IN OUT HRS Should be
8:42 AM 2:45 PM 4:48 6.00
8:50 AM 1:20 PM 14:24 4.50
8:45 AM 1:16 PM 14:24 4.50


Report •

#3
April 29, 2013 at 11:12:02
round up or down actual hrs worked in (D1)

Sorry for the confusion, the formula is for column D
you seemed to have everything else working.


MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 29, 2013 at 12:14:45
Mike,

Something is still wrong. When I use the formula given in column D it should come out to 6.00 but it's coming up with 7.20 if formatted as a number with 2 decimal places or 4:48 if custom h:mm.


Report •

#5
April 29, 2013 at 12:46:43
I copy/pasted your times from your post and using the formula I get:

       A            B         C
1) 8:42 AM     2:45 PM     6.00
2) 8:50 AM     1:20 PM     4.50
3) 8:45 AM     1:16 PM     4.50

Column C is formatted as Number, 2 Decimal places.


MIKE

http://www.skeptic.com/


Report •

#6
April 29, 2013 at 19:29:01
I must of typed something wrong before because it's working. Thank you soooo much!!!

Report •


Ask Question