• Sum hours in Excel (Office 365)

    • This topic has 2 replies, 3 voices, and was last updated 6 months ago.
    Author
    Topic
    #2718427

    I’m creating a timesheet. Trying to summarize hours in Excel.  I enter a start time and end time for each piece of work and get Excel to calculate the hours for that line.  I many rows of that.  That works fine.  Now I want to sum those ‘Hours’.  But after it gets to 23:15, for example, the next row is 2:30, but it returns 1:45 (because it’s beyond 24:00).  I want it to sum to 25:45.  How can that be done?  A digital answer would be OK (actually better), like 23.25 vs. 23:15 – either way is OK.

    Viewing 1 reply thread
    Author
    Replies
    • #2718439

      To get the hours in a format greater than 24, format the ‘sum()’ cell as [h]:mm, if you want the seconds use [h]:mm:ss. To get that as a decimal, if the sum() is in B1, you can use convert(B1,”day”,”hr”). If you don’t want the separate sum() showing just use convert(sum(<range>),”day”,”hr”).

    • #2718445

      Hours in Excel are actually a number less than 1, days are the number 1, so midnight = 0, noon = 0.5 and 18:00 = 0.75 etc.
      If you add noon and 18:00 you get 1.25, which is one day and 6 hours. Formatted as time it gives 06:00.

      The easiest way to force it to show in hours only is to use a custom format on the cell.
      [H]:MM

      cheers, Paul

    Viewing 1 reply thread
    Reply To: Sum hours in Excel (Office 365)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: