Calculating elapsed time with working hours.

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

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

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