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.

mmcconaghy, I posted more info in my question above; does that help?

Look here, down the bottom under the heading Rounding Times:http://www.cpearson.com/excel/datea...

See if that solves your problem.

MIKE

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!

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:00MIKE

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

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.00MIKE

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History