Thanks:  0
Likes:  0

# Thread: calculating time in quarter hours

1. ## calculating time in quarter hours

I am trying to calculate time based off a non-conventional quarter hour time system

example;
8:00 to 8:07 = 8.0 hrs
8:08 to 8:23 = 8.25 hrs
8:24 to 8:38 = 8.5 hrs
8:39 to 8:53 = 8.75 hrs
8:53 to 8:59 = 9.0 hrs

I am having trouble writing an equation that would sum the clock-in and clock-out times (2 per day) and display the total time in these quarter hours and adding an additional hour if it is >= to *:53. Any help would be great.

2. ## Re: calculating time in quarter hours

Hi ewmonty,

Perhaps the following will work for you. Assuming your clock-in time is in A1, and clock-out time is in B1:
Code:
`=INT((B1-A1)*24)+LOOKUP(MOD((B1-A1)*24,1),{0,0.12,0.4,0.65,0.88},{0,0.25,0.5,0.75,1})`

3. ## Re: calculating time in quarter hours

Hi and welcome to the board!!
=MROUND(A1*24,0.25)
Format cell as General
Analysis ToolPak Required

4. ## Re: calculating time in quarter hours

hi, well I'm coming in slow but this was a try assuming you have decimal values for the "Time Worked" (not hours/minutes):

Note: There's a problem in your table since 53 minutes is both .75 and 1 in the table. Also, it's not specific what happens to seconds (rounded to minutes, I assumed).

5. ## Re: calculating time in quarter hours

normally the quarter hour rounded would change at

7.5
22.5
37.5
52.5 minutes

your quarter hours appear to change at
7.5
23.5
38.5
53 ?

column B and C show normal rounding
column D should rounding to your numbers
there is the question at 8:53 as to what value you really want

Sheet8

 A B C D 1 8:00 8:00 8:00 8:00 AM 2 8:07 8:00 8:00 8:00 AM 3 8:08 8:15 8:15 8:15 AM 4 8:22 8:15 8:15 8:15 AM 5 8:23 8:30 8:30 8:15 AM 6 8:24 8:30 8:30 8:30 AM 7 8:37 8:30 8:30 8:30 AM 8 8:38 8:45 8:45 8:30 AM 9 8:39 8:45 8:45 8:45 AM 10 8:52 8:45 8:45 8:45 AM 11 8:53 9:00 9:00 9:00 AM 12 9:00 9:00 9:00 9:00 AM

 Cell Formula B1 =TIME(HOUR(A1),ROUND(MINUTE(A1)/15,0)*15,0) C1 =ROUND(MOD(A1,1)*96,0)/96 D1 =TIME(HOUR(A1),IF(MINUTE(A1)<8,0,IF(MINUTE(A1)<24,15,IF(MINUTE(A1)<39,30,IF(MINUTE(A1)<53,45,60)))),0)

Excel tables to the web >> Excel Jeanie HTML 4

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•