24 Hour Calculations

I was wondering if anyone has a solution for calculating (in 24hr Format):
Total Shift Hours per employee when start of shift is: 6/13/05 20:00 ending
6/14/05 04:00.
AND
Once shift totals are calculated in a column, subtotal both employee weekly
hours and company man - hours per shift.

If you have entered both the date and time, as you show, in a single cell,
then you can simply subtract the two and format as time to get the hours
worked.

As far as the subtotals are concerned, SUMIF will work for the totals by
employee.

If the starting times for each shift are identical for all employees (i.e.
20:00 for all), then you can use a formula like this (start times in column B,
end times in C, hours worked in column D):

=SUMPRODUCT((MOD(B1:B35),1)=20/24)*D1:D35)

If the times vary a bit, say between 20:00 and 21:00, you could try

=SUMPRODUCT((MOD(B1:B35),1)>=20/24)*(MOD(B1:B35),1)<=21/24)*(D1:D35))




On Tue, 14 Jun 2005 09:47:14 -0700, Nen <[email protected]> wrote:

>I was wondering if anyone has a solution for calculating (in 24hr Format):
>Total Shift Hours per employee when start of shift is: 6/13/05 20:00 ending
>6/14/05 04:00.
>AND
>Once shift totals are calculated in a column, subtotal both employee weekly
>hours and company man - hours per shift.

Here's the short answer.
Column A holds your labels:
A1 = "Start"
A2 = "End"
A3 = "Hours"

Column B holds the data:
B1 = 6/13/05 20:00
B2 = 6/14/05 04:00
Note these two cells should be formatted as timestamps in 24 hour
format.
B3 is a formula: =(A2 - A1)*24
B3 should be formatted as a number with as many significant digits as
you require. The result in B3 should read 8.0.

Here's the long answer: Excel treats dates as numbers and displays them
in familiar date formats. Pick any cell, call it D1, and enter today's
date: 6/14/2005. Excel recognizes this as a date and automatically
formats it as such. However, if you right click cell D1 and reformat
it as a number, you'll see the value Excel assigns to today's date is
38517. The value of a single day is 1; to track the time of day, Excel
uses a fraction of 1. The numeric value of 12:00 noon on June 14 2005
is 38517.5 (half a day = half of 1 = 0.5). The formula in B3
multiplies by 24 to convert the partial day into hours.

How you handle subtotals of employee weekly hours and man-hours per
shift depends on the layout of your information: anyone here on the NG
will be glad to help, but how you have it now will determine the best
way to treat it. Let us know if we can help!