Calculating elapsed time with working hours.

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:

DUR2DAYS(NETWORKDAYS(B2,C2,))*9.5

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.

-24*(TIMEVALUE(B2)-TIMEVALUE("1/1/1 07:30"))

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.

-24*(TIMEVALUE("1/1/1 17:00")-TIMEVALUE(C2))

This part calculates and subtracts the number of hours not worked at the end of the finish date.

Regards,

Barry

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.

Mat,

Will the working period ever exceed one week?

Jerry

Rollemtam wrote:

Very rarely but looking at my data, yes on occasion I have an 8 day job and a 20 day job.

Mat.

Mat,

Also, will you need to subtract some number hours per day to account for lunch breaks, etc.? And if so, will there be rules governing that deduction?

Jerry

Hi Mat,

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.

Jerry

I guess if you keep a separate record for Saturdays, that would take care of it too.

Jerry

Hi Mat,

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:

Select D2.

In the entry bar, place the insertion point after the = sign. Type the characters below:

DURATION(,,

Click Accept.

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)))

Fill down.

Format as described above.

Regards,

Barry

Hi Mat,

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"

Regards,

Barry