## Calculating elapsed time with working hours.

1322 Views 23 Replies Latest reply: Apr 21, 2013 5:27 AM by Rollemtam
Previous Next
Calculating status...
Currently Being Moderated
Apr 1, 2013 12:12 PM

I am trying to calculate the time elapsed from the start of a job to the end. I only want to calculate the time elapsed between working days (Mon-Fri) and working hours (07:30-17:00).

So If the start time was in cell A2, the end time in B2, I need the formula to work out the time elapsed in C2.

The start and end dates may be on different days, and I would like the result in C2 to be formatted into days, hours, minutes.

I'm not too savvy with numbers formulas that go beyond adding, subtracting and averaging so I'm not entirely sure if this is a possibility but I will appreciate any help available.

Cheers,

Mat

• Level 7 (28,135 points)
Currently Being Moderated
Apr 1, 2013 1:00 PM (in response to Rollemtam)

Mat,

Will the working period ever exceed one week?

Jerry

• Level 7 (29,095 points)
Currently Being Moderated
Apr 1, 2013 1:34 PM (in response to Rollemtam)

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.

• Level 7 (28,135 points)
Currently Being Moderated
Apr 1, 2013 4:48 PM (in response to Rollemtam)

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

• Level 7 (28,135 points)
Currently Being Moderated
Apr 5, 2013 6:06 AM (in response to Rollemtam)

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

• Level 7 (28,135 points)
Currently Being Moderated
Apr 5, 2013 10:39 AM (in response to Rollemtam)

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

Jerry

• Level 7 (29,095 points)
Currently Being Moderated
Apr 6, 2013 3:11 AM (in response to Rollemtam)

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

• Level 7 (29,095 points)
Currently Being Moderated
Apr 6, 2013 9:34 AM (in response to Rollemtam)

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

Previous Next

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.