# 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.