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 hour
to 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

Report •


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

MIKE

http://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.

MIKE

http://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 A
Formula 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

MIKE

http://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)/4

My 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.00
08:00 AM 8:00 PM 12.00
10:40 AM 9:30 PM 10.75
10:30 AM 9:30 PM 11.00
12: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)*24

Put the formula in Column C
Format 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

MIKE

http://www.skeptic.com/


Report •


Ask Question