Adding up hours

  • Thread starter Thread starter John Phillips
  • Start date Start date
J

John Phillips

Hi all...
I need to create a spreadsheet where my workers type in
the time the start a job, and the time they finish it. I
need a formula to calculate how long they worked on it in
hours and minutes (anywhere from 1-15 hours). I also need
a total of those hours. I seem to have figured out the
first part, but can not get the hours to be summed.

Any ideas?
 
Try a pivot table. I used the following table
Name Job Start Finish
abc 123 8:00 AM 12:00 PM =(d2-c2)*24
abc 345 12:00 PM 3:00 PM
abc 678 3:00 PM 6:00 PM
DEF 123 8:00 AM 12:00 PM
DEF 345 12:00 PM 3:00 PM
DEF 678 3:00 PM 6:00 PM

highlight the entire table >data>pivot table
 
Format the sum cell as [hh]:mm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi all...
Things are looking up a bit... but when I subtract 8:30
from 17:15, I get 8:45 instead of 8.75. Can 8:45 be
converted to 8.75?
Thanks
John
 
Multiply it by 24 and format as General

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi John,

Suppose you have a spreadsheet with 4 columns: Col B will have you
Worker's name, column C is the Start time (e.x. 06:25), column D is th
End time and, finally, column E is the Difference.

Say you have 5 workers, whose names start at cell B4 and go down t
cell B8.
Format cells C4:E8 as hh:mm.
In cell E4 put the formula =D4-C4+IF(C4>D4,1) and copy it all way dow
to cell E8.

Now, in cell E9 type this formula: =SUM(E4:E8) and format the cell a
[h]:mm.

That's it! :)

Paul Ger
 
Back
Top