1 2 Previous Next 23 Replies Latest reply: Apr 21, 2013 5:27 AM by Rollemtam Go to original post
• ###### 15. Re: Calculating elapsed time with working hours.
Level 1 (0 points)

Brilliant,

Thanks again for all your help Barry.

And thanks to Jerry also!

Mat

• ###### 16. Re: Calculating elapsed time with working hours.
Level 1 (0 points)

On testing this formula i've hit a few issues.

Some of the results in column F have an added 30mins. This particular issue is shown in your column F3 above. My maths make it 3wd, 7hr, 0mn. This doesn't happen on each result so it could possibly be my maths.

Also if you have a time that isn't on the hour or 30mins past the hour the minutes has multiple decimal points, not sure how to alter this.

Mat

• ###### 17. Re: Calculating elapsed time with working hours.
Level 7 (29,180 points)

Hi Mat,

"My maths make it 3wd, 7hr, 0mn. This doesn't happen on each result so it could possibly be my maths."

Possibly, but I think it's an error in the formula, one of the issues that arise from tacking on bits as the problem definition gets revised.

"Also if you have a time that isn't on the hour or 30mins past the hour the minutes has multiple decimal points, not sure how to alter this."

One of the hazards of flipping between Date and Time, Duration, and number values and doing base 2 arithmetic then converting to base 10 display. Easily corrected, fortunately.

Here's a rejig, using a slightly different approach. I haven't boiled it down (or stitched it together) into a single formula, so there are a few columns that could be hidden if desired.

Descriptions below:

All formulas filled down their columns from row 2 to row 10.

D2: =DUR2DAYS(NETWORKDAYS(B2,C2,))-2+INT(E2/9.5)

This calculates the number of work days between the start and finish dates, then subtracts 2 (the starting day and the finishing day). The last part adds 1 for each 9.5 hours worked on the starting day + finishing day.

E2: =24*(TIMEVALUE("1/1/1 17:00")-TIMEVALUE(B2))+24*(TIMEVALUE(C2)-TIMEVALUE("1/1/1 07:30"))

The first part (up to the + sign) calculates a number equivalent to the number of hours worked on the starting day; the second part does the same for the finishing day. The two calculations are added.

F2: =IF(E2<9.5,E2,E2-(9.5*INT(E2/9.5)))

If the hours total for the starting plus finishing days is less than a full working day (9.5 hrs), that amount is placed in E2; otherwise, calculates the number of full days equivalent worked in those two days, multiplies the result by 9.5, subtracts that many hours from the amount in E2, and places the result in F2. The subtracted hours have also bee converted to the equivalent in workdays in column E.

G2: =D&"wd "&INT(F)&"hr "&ROUND(60*(F-INT(F)),0)&"mn"

The three parts of this formula create the wd hr mn string reporting time worked.

The first part retrieves the number of working days from column D and appends the text wd "

The second part extracts the whole number of hours from column F and appeands the text "hr "

The third part subtracts the whole number of hours from column F, and multiplies the fractional part by 60 to determine the number of minutes, rounds this value to the nearer whole minute, and appends the text "mn".

Further test below is to check the transition between less than and more than 9.5 hours on the start + finish days.

Regards,

Barry

• ###### 18. Re: Calculating elapsed time with working hours.
Level 1 (0 points)

Tested and working great!

Thanks!

• ###### 19. Re: Calculating elapsed time with working hours.
Level 1 (0 points)

Afternoon Barry,

I'm after a slight variation on your above formula.Which by the way is working great and as I'd wanted.

If for instance I had a duration in a cell, 0d 9.5h 0m, how would I then calculate that into the working day format of 1wd 0hr 0mn?

I don't have a start and end date for this particular calculation as was the case for your previous formula.

Cheers,

Mat.

• ###### 20. Re: Calculating elapsed time with working hours.
Level 7 (29,180 points)

Hi Mat,

How did you get that particular value into the cell?

Any duration value I've seen includes either no fractional parts or a fractional part only for the smallest unit. Your example, if it is a duration value formatted as 0d 0h 0m, would be displayed as 0d 9h 30m.

Regards,

Barry

• ###### 21. Re: Calculating elapsed time with working hours.
Level 1 (0 points)

Barry I apologise for my earlier error. I don't have a cell with 9.5hr in. I was making an example and was obviously half asleep when typing it.

I'm after the same results as in the wds hrs mns column in your example table above. I don't have a start and end date column now just a duration column in 0d 0h 0m format. I wasn't sure how to alter your formula to suit this.

This is a separate table for different results.

Hope I now make more sense.

Mat

• ###### 22. Re: Calculating elapsed time with working hours.
Level 7 (29,180 points)

Hi Mat,

I think this does what you're looking for:

Note the two blue-filled rows (and the two below them). Provided the column (B) contains duration values, the format in which they are shown does not affect the calculations.

The formula is rather long, so I've pasted it in then pasted a second copy split into lines showing the structure a bit more clearly.

C2: =INT(DUR2HOURS(B)/9.5)&" wd "&INT(DUR2HOURS(B)-9.5*INT(DUR2HOURS(B)/9.5))&" hr "&ROUND(60*(DUR2HOURS(B)-9.5*INT(DUR2HOURS(B)/9.5)-INT(DUR2HOURS(B)-9.5*INT(DUR 2HOURS(B)/9.5))),0)&" mn "

=INT

(DUR2HOURS(B)/9.5)&" wd "

&INT

(DUR2HOURS(B)-9.5*INT(DUR2HOURS(B)/9.5))&" hr "

&ROUND(60*

(DUR2HOURS(B)-9.5*INT(DUR2HOURS(B)/9.5)-INT(DUR2HOURS(B)-9.5*INT(DUR2HOURS(B)/9. 5))),0)&" mn "

Regards,

Barry

• ###### 23. Re: Calculating elapsed time with working hours.
Level 1 (0 points)

Super!

Done the job perfectly.

Thanks again!

Mat

1 2 Previous Next