Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: calculating time in quarter hours

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by ewmonty; Aug 21st, 2009 at 05:46 PM.

  2. #2
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: calculating time in quarter hours

    Hi and welcome to the board!!
    =MROUND(A1*24,0.25)
    Format cell as General
    Analysis ToolPak Required
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default 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).

    Download Sample Workbook

    excel timesheet
    Last edited by xenou; Aug 21st, 2009 at 06:20 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Jun 2008
    Location
    North Carolina
    Posts
    2,465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    ABCD
    18:008:008:008:00 AM
    28:078:008:008:00 AM
    38:088:158:158:15 AM
    48:228:158:158:15 AM
    58:238:308:308:15 AM
    68:248:308:308:30 AM
    78:378:308:308:30 AM
    88:388:458:458:30 AM
    98:398:458:458:45 AM
    108:528:458:458:45 AM
    118:539:009:009:00 AM
    129:009:009:009:00 AM

    Spreadsheet Formulas
    CellFormula
    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
    May I deal with Honor
    May I act with Courage
    May I strive for Humility
    "Straight" By **** Francis

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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