Home › Forums › Money & Finance › Excel Q – Adding up times
- This topic has 5 replies, 4 voices, and was last updated 9 years, 4 months ago by –.
-
AuthorPosts
-
November 12, 2014 8:38 pm at 8:38 pm #614228SayIDidIt™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™
November 12, 2014 9:21 pm at 9:21 pm #1041863ED IT ORParticipantTry autosum
November 12, 2014 9:59 pm at 9:59 pm #1041864SayIDidIt™ParticipantAnd his would I do that? (I don’t know Excel so much…)
November 12, 2014 11:43 pm at 11:43 pm #1041865JaneDoe18ParticipantIn the cell where you want the total,
type
=SUM(E2:E32)
November 12, 2014 11:47 pm at 11:47 pm #1041866–ParticipantIt 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.
November 13, 2014 12:32 am at 12:32 am #1041867–ParticipantIn 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”
-
AuthorPosts
- You must be logged in to reply to this topic.