Excel Q – Adding up times

Home Forums Money & Finance Excel Q – Adding up times

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #614228
    SayIDidIt™
    Participant

    I am trying to make a timesheet in Excel. I have three columns: In (C), Out (D) and Total (E). All cells are formatted for 12 hour time.

    In Total I have “=Out-In” (ex. D2-C2). This works well.

    Now I want F to total the whole month. I tried doing ex. =E2:E32 but that gives me a very low number (like 8:10). I tried putting E in ( ) and got this huge number… Basically, I tried a bunch of things unsuccessfully.

    If anyone knows how to set it up, please let me know. Thanks in advance!

    SiDi™

    #1041863
    ED IT OR
    Participant

    Try autosum

    #1041864
    SayIDidIt™
    Participant

    And his would I do that? (I don’t know Excel so much…)

    #1041865
    JaneDoe18
    Participant

    In the cell where you want the total,

    type

    =SUM(E2:E32)

    #1041866
    Participant

    It has to do with how Excel stores times internally. I could go into a lengthy discussion about how Excel stores time values but instead I’ll just tell you how to fix it. Change the format of column E to a decimal number and change the formulas in column E to “=(D2-C2)*24”. This will give you a decimal representation for the hours. If you need to see the actual minutes or want an in depth explanation let me know.

    #1041867
    Participant

    In the cell where you want the total,

    type

    =SUM(E2:E32)

    That won’t work due to the way Excel stores its dates. Lets say you work from 9-5 5 days a week. When you enter “9:00 AM” Excel stores the value of 0.375 which is short for “1/0/1900 9:00:00” and when you enter “5:00 PM” it stores the value of 0.708333 (or “1/0/1900 17:00:00”). When you subtract these you’ll end up with 0.333 (or “1/0/1900 8:00:00”). If you add up the entire week you’ll end up with 1.666 (or “1/1/1900 16:00:00”). If you attempt to display this in 12 hour format it will just display as “4:00 PM”

Viewing 6 posts - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.