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

Thread: Adding hours to get a decimal #

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

    Default

    Im am trying to add hours together to get a decimal number. Ie: if I work 9:45 - 14:00 the answer should be 4.25 hrs. is there a way to get excel to do this.

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,157
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    multiply the result by 24

    paddy

    EDIT: on reflection, that was a bit terse! make sure you check out the help file for How Excel stores dates & times. see also:

    http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

    and

    http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

    for loads of info on date / time calculations

    [ This Message was edited by: PaddyD on 2002-11-11 19:03 ]

  3. #3
    New Member
    Join Date
    Nov 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi alexhewett


    I have a formula that i'm working right now give about 5 mins and I'll put it up..testing it right now to see it works ok...

  4. #4
    New Member
    Join Date
    Nov 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi alexhewett

    Start Finished total hrs work
    9:45 14:00 4:15


    In excel I used the insert function and used ABS. In the rows of the work sheet I formated the cells with format cell then used custom it was h:mm..I was off by 0:10
    ...Hope this helps you figure it out....I was close but I think that only counts in horse shoes...hhahaha..

    [ This Message was edited by: Gemini on 2002-11-11 19:30 ]

  5. #5
    Board Regular
    Join Date
    May 2002
    Posts
    890
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another approach

    =CEILING(((B1-A1+(B1 < A1))*24),0.01)

    This formula takes into account 10:00pm to 1:00am also.


  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi alexhewett:

    Welcome to the Board!
    If the hours are within a 24 hour range, you can use ...
    '=(B1-A1+(A1>B1)+0)*24
    and format the result as Number with 2 decimal places
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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
  •  


DMCA.com