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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bluzdog

    Quarter of the hour formula

    Bluzdog, Sep 13, 2003, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    639
    JohnI in Brisbane
    Sep 13, 2003
  2. Guest
    Replies:
    1
    Views:
    211
    Guest
    Feb 11, 2006
  3. Guest
    Replies:
    4
    Views:
    167
    Guest
    Sep 11, 2007
  4. Replies:
    4
    Views:
    377
    MartinW
    Sep 2, 2008
  5. Google Rocks

    Quarter Hour time conversions for timesheet

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

Share This Page