# Need a formula to calculate quarter hours

December 5, 2011 at 12:05:18
Specs: Windows XP
 I have Excel 2003. How do I create a formula to calculate in/out times in quarter hours as time worked? I.E., to enter a time that needs to be converted to quarters of an hour. For 8 to 22 minutes past the hour = x:15, for 23-37 minutes past the hour = x:30, For 38 to 52 minutes past the hour =x:45, and For 53 minutes before the hourto 7minutes past the hour = x:00. Many may ask Why? Its the way its always been done by hand.

See More: Need a formula to calculate quarter hours

#1
December 5, 2011 at 12:55:56
 Your going to have to be a bit more specific.MIKEhttp://www.skeptic.com/

Report •

#2
December 5, 2011 at 13:15:31
 mmcconaghy, I posted more info in my question above; does that help?

Report •

#3
December 5, 2011 at 13:19:27
 Look here, down the bottom under the heading Rounding Times:http://www.cpearson.com/excel/datea...See if that solves your problem.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
December 5, 2011 at 16:02:22
 No, I'm a novice to creating formulas etc. in Excel; just basic stuff, but I did find and figure out a formula to copy and adjust so that it worked in my worksheet. Thanks!

Report •

#5
December 5, 2011 at 16:24:01
 OK,Just to make sure, your formula should look something like:=TIME(HOUR(A1),MROUND(MINUTE(A1),B1),0)or=TIME(HOUR(A1),MROUND(MINUTE(A1),15),0)If your data looks like this:Time in column AFormula in column B > =TIME(HOUR(A1),MROUND(MINUTE(A1),15),0)``` A B 1) 1:01 1:00 2) 1:07 1:00 3) 1:08 1:15 4) 1:22 1:15 5) 1:23 1:30 6) 1:37 1:30 7) 1:38 1:45 8) 1:52 1:45 9) 1:53 2:00 10) 1:59 2:00 ```MIKEhttp://www.skeptic.com/

Report •

#6
December 5, 2011 at 18:23:52
 This is the formula I ended up with: =ROUND(SUM((C10-B10))*96,0)/4My data example below, I wanted the total hours to come out in quarter hour increments as in your example, so this worked great. Thanks! Log in Log Out Total Hours 09:30 AM 9:30 PM 12.0008:00 AM 8:00 PM 12.0010:40 AM 9:30 PM 10.7510:30 AM 9:30 PM 11.0012:00 PM 9:00 PM 9.00

Report •

#7
December 5, 2011 at 18:53:31
 You could also use this:=TIME(HOUR(SUM(B1-A1)),MROUND(MINUTE(SUM(B1-A1)),15),0)*24Put the formula in Column CFormat Column C as Number with 2 decimal places.``` A B C 1) 9:30 AM 9:30 PM 12.00 2) 8:00 AM 8:00 PM 12.00 3) 10:40 AM 9:30 PM 10.75 4) 10:30 AM 9:30 PM 11.00 5) 12:00 PM 9:00 PM 9.00 ```MIKEhttp://www.skeptic.com/

Report •