- This topic has 5 replies, 4 voices, and was last updated 10 years ago by .
-
Posts
-
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™
Try autosum
In the cell where you want the total,
type
=SUM(E2:E32)
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.
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”
- You must be logged in to reply to this topic.