Previous 1 2 23 Replies Latest reply: Apr 21, 2013 5:27 AM by Rollemtam
Level 1

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

Solved by Barry on Apr 1, 2013 1:34 PM Solved
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.

All replies

• Level 7

Mat,

Will the working period ever exceed one week?

Jerry

• Level 1

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

Mat.

• Level 7

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 1

Looks great thanks Barry!

I'll keep you posted on my results when I test it tomorrow.

• Level 7

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 1

Hi Jerry, bit slow getting back to you. I don't need to account for lunch breaks as there is 4hrs of breaks in the week and we work 4hrs on a Saturday so it cancels itself out and creates less formula I assume.

• Level 1

That formula works great Barry.

Just having trouble trying to format the results into 0d 0h 0m

Any suggestions?

Thanks,

Mat

• Level 7

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 1

Hi Jerry,

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.

Cheers,

Mat.

• Level 7

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

Jerry

• Level 1

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!

Thanks,

Mat.

• Level 7

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 1

Barry that's super thanks!

Is there an easy solution to convert the duration from calendar days to working days.

For example your cell E3 would become 3 working days and 7 hours.

I won't ask any more of you after this!

Thanks again,

Mat

• Level 7

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 1 2