Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 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. |
#3
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) | |||
Round Time with Conditions | Charts and Charting in Excel | |||
How can I round an hour to the nearest 1/4 hour? | Excel Worksheet Functions | |||
convert 100 minute hour to a 60 minute hour | Excel Worksheet Functions |