how to calculate time start & time finish in quarter hour

Discussion in 'Microsoft Excel Misc' started by Guest, Jun 6, 2006.

  1. Guest

    Guest Guest

    I would like to show the difference between start and end time in hours and
    quarter in Excel 2003. Example:

    Start time End time Result
    15.30 1.15 9.85

    I would like to see the result column showing 9 hours and 3 quarters,
    instead of 9.85 hrs. Can anyone help. Im using Microsoft
     
    Guest, Jun 6, 2006
    #1
    1. Advertisements

  2. Guest

    Peo Sjoblom Guest

    I don't see how 9.85 hours can ever be 9 hours and 3 quarters?

    If you use start time 15:30 and end time 01:15, that gives a result of 9:45
    or 9.75 using decimals, that is 3 quarters

    you can round it to the nearest 0.25 and then use

    =MOD(ROUND(A1*4,0)/4,1)/0.25

    --

    Regards,

    Peo Sjoblom

    Excel 95 - Excel 2007
    Northwest Excel Solutions
    www.nwexcelsolutions.com
    "It is a good thing to follow the first law of holes;
    if you are in one stop digging." Lord Healey


    "Peter Wu" <Peter > wrote in message
    news:...
    >I would like to show the difference between start and end time in hours and
    > quarter in Excel 2003. Example:
    >
    > Start time End time Result
    > 15.30 1.15 9.85
    >
    > I would like to see the result column showing 9 hours and 3 quarters,
    > instead of 9.85 hrs. Can anyone help. Im using Microsoft
     
    Peo Sjoblom, Jun 7, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Click in the cells you want to be formatted that way then go to:
    Format->Cells->Fraction->As Quarters

    This will result in 9.85 being displayed as 9 3/4

    -DD

    "Peter Wu" wrote:

    > I would like to show the difference between start and end time in hours and
    > quarter in Excel 2003. Example:
    >
    > Start time End time Result
    > 15.30 1.15 9.85
    >
    > I would like to see the result column showing 9 hours and 3 quarters,
    > instead of 9.85 hrs. Can anyone help. Im using Microsoft
     
    Guest, Jun 7, 2006
    #3
  4. Guest

    Guest Guest

    Try this:
    =INT((EndTime-StartTime)/1)&" hours "&INT(MOD((EndTime-StartTime),1)/0.25)&"
    quarters"

    -Simon

    "Peter Wu" wrote:

    > I would like to show the difference between start and end time in hours and
    > quarter in Excel 2003. Example:
    >
    > Start time End time Result
    > 15.30 1.15 9.85
    >
    > I would like to see the result column showing 9 hours and 3 quarters,
    > instead of 9.85 hrs. Can anyone help. Im using Microsoft
     
    Guest, Jun 7, 2006
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Bluzdog

    Quarter of the hour formula

    Bluzdog, Sep 13, 2003, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    768
    JohnI in Brisbane
    Sep 13, 2003
  2. Guest

    convert time from 60 minute hour to 100 minute hour

    Guest, Jul 6, 2005, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    1,835
    Morrigan
    Jul 6, 2005
  3. Guest
    Replies:
    1
    Views:
    342
    Guest
    Feb 11, 2006
  4. Guest
    Replies:
    4
    Views:
    260
    Guest
    Sep 11, 2007
  5. SueSea

    ROUND UP HOURS to the next quarter hour

    SueSea, Feb 27, 2008, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    810
    Conan Kelly
    Feb 27, 2008
  6. Trudy

    Round result of sum function to quarter hour

    Trudy, Mar 4, 2008, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    268
    T. Valko
    Mar 4, 2008
  7. Replies:
    4
    Views:
    657
    MartinW
    Sep 2, 2008
  8. Google Rocks

    Quarter Hour time conversions for timesheet

    Google Rocks, Feb 8, 2010, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    430
    Mike H
    Feb 8, 2010
Loading...