This should work. I haven't accounted for holidays, but that's pretty easily done.
Starting date and time in column B, Finish date and time in column C.
Formula in D2, and filed down the column:
D2: =DUR2DAYS(NETWORKDAYS(B2,C2,))*9.5-24*(TIMEVALUE(B2)-TIMEVALUE("1/1/1 07:30"))-24*(TIMEVALUE("1/1/1 17:00")-TIMEVALUE(C2))
The result is a number, representing the number of working hours in the period. The formula has three parts:
NETWORKDAYS returns a duration value equivalent to the number of workdays in the period.
DUR2DAYS converts that duration to a number.
*9.5 multiplies the number of days by the number of hours worked in a full day.
TIMEVALUE extracts the time of day from the value in B2 and returns it as a decimal fraction of the 24 hour day that has passed since midnight. The second TIMEVALUE does the same for the date and time value "1/1/1 07:30", the starting time of the working day.
The difference between the two time values is multiplied by 24 to determine the number of hours not worked at the beginning of the first day. This is subtracted from the result above.
This part calculates and subtracts the number of hours not worked at the end of the finish date.
PS: Holidays are handled in the NETWORKDAYS function. See the iWork Formulas and Functions User Guide for details. The guide may be downloaded via th Help menu in Numbers '09.
I like Barry's formula too, especially for it's brevity. As he may tell you, I'm particularly fond of elegant expressions that maximize readability, and as you can see from his explanation, his approach is easily understood.
The only concern I have about Barry's solution is that it fails if the Clock-In and Clock-Out times are beyond the 7:30 Hours to 17:00 Hours window. I've put a lot of effort into trying to come up with a solution that allows your workers to clock in or out whenever they please, arriving early for work or punching out late, or even hitting the clock on weekends. My expressions to handle that are horrendous. Maybe Barry can come up with something simple for those situations on his basic framework. I'm be ashamed to show you my messy solution, but if it turns out to be important to you, I'll continue to work on it.
I'm a bit confused about your comment on Saturday work since I thought from your original problem statement that weekends were out. If the Saturday work is just "off the books", I understand.
Basically we work 07:30 - 17:00 Mon to Fri with four hours of breaks in total. We also work 08:00 - 12:00 on Saturdays with no breaks. My theory was that if i didn't include the saturday hours and the week breaks I would still be looking at a 45hr week and getting similar results. It was more to do with the fact I had no clue whatsoever how to accomodate breaks / saturdays into a formula so I left it out.
If I can get a successful formula to accomodate Mon-Fri including any early clock-in / clock-outs as you mentioned I would be more than happy to forget Saturday as the colleague who this report is intended for very rarely works a Saturday.
Hope this is of use.
Jerry / Barry,
I've tested Barrys formula this morning and it seems to be working great, even if the job starts / finshes outside of the working hours. Adding break times to the formula is not essential but I do need the results column to be formatted into days, hours, minutes. I can't get any change other than the results showing in hours.
Hope the experts can help!
Formula in column E is =DURATION(,,D)
Note the two commas before the D. Resul wil initially report only hours. Next step is to format the cell to show days, hours and minutes. Grab the dot at the righ end of the bar and drag it right to include Hr Min Sec, then grab the center dot and drag left one notch to cover Day Hr Min.
I used an extra column to keep the formula simple. You can do the entire calculation in column D with a simple edit:
In the entry bar, place the insertion point after the = sign. Type the characters below:
The formula should now look like this:
=DURATION(,,DUR2DAYS(NETWORKDAYS(B2,C2,))*9.5-24*(TIMEVALUE(B2)-TIMEVALUE("1/1/1 07:30"))-24*(TIMEVALUE("1/1/1 17:00")-TIMEVALUE(C2)))
Format as described above.
The values in E are durations, so a "Day" is 24 hours. I don't see any way to change that other than speeding the rotation of the earth and redefining the Day as 9.5 hours. That might be a bonus to people trying to reduce their weight, especially those living near the equator, but would certainly wreak havoc on sleep patterns for everyone.
You can have a formula that will rewrite the number of hours (in column D, original table) into a text string showing the number of working days, hours and minutes. The string will not be useable in further calculations without formulas that extract the numbers from that value, though.
Formula shown above the table. Results are in column F. I used a two letter identifier for dy, hr and mn to distinguish this from a duration value.
Here's the formula again, in a copyable form:
=IF(D>=9.5,INT(D/9.5)&"wd, ","")&INT(D-9.5*INT(D/9.5))&"hr, "&60*(D-INT(D))&"mn"