Calculate Time

I need to have reps enter start times, end times and total pieces worked in 3 separate cells. I need to calculate the sums for total time spent on each task and total pieces worked. Lastly I want to calculate total pieces worked per hour.

I want it to look something like this:

Start Time End Time Total Pcs Worked
8:00 8:30 10
10:45 11:15 25
3:20 4:20 15

Total Time 2:00
Total Pcs 50
Pcs p/Hr 25

Last question am I able to format time without having reps to a) not use military time and b) not have them enter time as AM/PM and still have excel calculate accurately?

Thanks!

You can choose a different date format in the Format Cells dialog box, as explained in the following procedure. Similarly, if you type 9:30 a or 9:30 p in a cell, Excel will interpret this as a time and display 9:30 AM or 9:30 PM. Again, you can customize the way the time appears in the Format Cells dialog box.

Please check the link and hope will help you!
https://support.office.com/en-us/art...a-b4d30c528309

I would question the Pcs p/Hr side of things.

For example, if the total time was 4:45 and there were 50 pieces of work. Would that be 12.5 per hr because there were 4 whole hours or 10.5 if you take into account the additional 45 mins?

BSB

This is perfect- thanks!

I will be going with the one on the right to keep it simple for my reps. We're much better off limiting keystrokes with this group.

Happy New Year!

Yes 10.5... we need to have the Pcs/Hr be as accurate as possible and whole hours doesn't get us there.

Originally Posted by mikeTRON

I assumed 10.5.

Me too. But does that not mean the formula in cells J4 and V4 in your sample workbook are doing something different as they're dividing the total by just the hour element of the total time.

Something like =(I4/H4)/24 in J4 would work and provide the answer we both assumed.

BSB

See the attachment. I think I got it calculating correctly irrespective of whether the input is AM or PM.