Calculating working hours

[aoife]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Completion Date
[/TD]
[TD]Completion Time
[/TD]
[TD]Reported date
[/TD]
[TD]Reported time
[/TD]
[/TR]
[TR]
[TD]01.05.2019
[/TD]
[TD]12:07:06
[/TD]
[TD]01.05.2019
[/TD]
[TD]11:25:27
[/TD]
[/TR]
[TR]
[TD]02.05.2019
[/TD]
[TD]15:24:49
[/TD]
[TD]02.05.2019
[/TD]
[TD]14:16:31
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

Any suggestions how I calculate working hours taken from reported to completion.
Working hours are 8.30am to 5pm and don't include weekends (date is in UK format)

Thanks in advance for any help

Aoife

 

[tyija1995]

Hi,

Do some of the reported date/times and completion date/times happen NOT in working hours?

I.e. can a reported time be 06:13:25 for example? Or will these parameters always be within 08:30:00 - 17:00:00 with dates mon-fri?

 

[aoife]

Some of the times will happen outside of working hours.
I've got so far with this: (don't know if you can see it sorry)

Hi,

Do some of the reported date/times and completion date/times happen NOT in working hours?

I.e. can a reported time be 06:13:25 for example? Or will these parameters always be within 08:30:00 - 17:00:00 with dates mon-fri?

 

[tyija1995]

Some of the times will happen outside of working hours.
I've got so far with this: (don't know if you can see it sorry)

I can't see that image - however if you are open to using helper columns then I have a solution for this (hopefully!!)

To simplify the issue of whether the time/date is within mon-fri 8:30-17:00 setup a helper column for each of the columns currently (so you have 4 columns initially, make 4 more - i'd suggest adding a prefix such as "Imp. "

So if A1:D1 currently are your headers, make E1:H1 the Imp. headers

With the data stored in 2nd row (A2:D2) use the following formulae for E2:H2

E2:
=IF(TEXT(A2,"ddd")="Sat",A2+2,IF(TEXT(A2,"ddd")="Sun",A2+1,A2))

F2:
=IF(E2>A2,8.5/24,IF(B2<8.5/24,8.5/24,IF(B2>17/24,17/24,B2)))

G2:
=IF(TEXT(C2,"ddd")="Sat",C2+2,IF(TEXT(C2,"ddd")="Sun",C2+1,C2))

H2:
=IF(G2>C2,8.5/24,IF(D2<8.5/24,8.5/24,IF(D2>17/24,17/24,D2)))

Finally the Working Hours (Column I) cell I2:
=(NETWORKDAYS.INTL(G2,E2,1)-1)*8.5+(F2-H2)*24

Format as a number.

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Completed Date[/TD]
[TD]Completed Time[/TD]
[TD]Report Date[/TD]
[TD]Report Time[/TD]
[TD]Imp. Comp Date[/TD]
[TD]Imp. Comp Time[/TD]
[TD]Imp. Report Date[/TD]
[TD]Imp. Report Time[/TD]
[TD]Working Hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]05/05/2019[/TD]
[TD]12:07:06[/TD]
[TD]02/05/2019[/TD]
[TD]11:25:27[/TD]
[TD]06/05/2019[/TD]
[TD]08:30:00[/TD]
[TD]02/05/2019[/TD]
[TD]11:25:27[/TD]
[TD]14.08[/TD]
[/TR]
</tbody>[/TABLE]

This shows the table in an example (random data entered for A2:D2 - the working hours should be correct though).

 

[aoife]

Absolutely Fantastic!!!!! Fianlly feel like I'm getting somewhere, I'm just struggling with columns H and I now. I seem to get either a 17:00:00 or 08:30:00 back and for column I that means it's giving me a negative figure!
Do you think it's to do with the way I've formatted it?

Thank you so much.

Aoife

I can't see that image - however if you are open to using helper columns then I have a solution for this (hopefully!!)

To simplify the issue of whether the time/date is within mon-fri 8:30-17:00 setup a helper column for each of the columns currently (so you have 4 columns initially, make 4 more - i'd suggest adding a prefix such as "Imp. "

So if A1:D1 currently are your headers, make E1:H1 the Imp. headers

With the data stored in 2nd row (A2:D2) use the following formulae for E2:H2

E2:
=IF(TEXT(A2,"ddd")="Sat",A2+2,IF(TEXT(A2,"ddd")="Sun",A2+1,A2))

F2:
=IF(E2>A2,8.5/24,IF(B2<8.5/24,8.5/24,IF(B2>17/24,17/24,B2)))

G2:
=IF(TEXT(C2,"ddd")="Sat",C2+2,IF(TEXT(C2,"ddd")="Sun",C2+1,C2))

H2:
=IF(G2>C2,8.5/24,IF(D2<8.5/24,8.5/24,IF(D2>17/24,17/24,D2)))

Finally the Working Hours (Column I) cell I2:
=(NETWORKDAYS.INTL(G2,E2,1)-1)*8.5+(F2-H2)*24

Format as a number.

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Completed Date[/TD]
[TD]Completed Time[/TD]
[TD]Report Date[/TD]
[TD]Report Time[/TD]
[TD]Imp. Comp Date[/TD]
[TD]Imp. Comp Time[/TD]
[TD]Imp. Report Date[/TD]
[TD]Imp. Report Time[/TD]
[TD]Working Hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]05/05/2019[/TD]
[TD]12:07:06[/TD]
[TD]02/05/2019[/TD]
[TD]11:25:27[/TD]
[TD]06/05/2019[/TD]
[TD]08:30:00[/TD]
[TD]02/05/2019[/TD]
[TD]11:25:27[/TD]
[TD]14.08[/TD]
[/TR]
</tbody>[/TABLE]

This shows the table in an example (random data entered for A2:D2 - the working hours should be correct though).

 

[tyija1995]

Hey, thanks for the feedback.

Could you give me an example of a row of data in A:D that throws a negative output in column I? - I'll test it and see if I need to make any amendments!

 

[aoife]

Hi,
I've used exactly the same data as the sample used, I'm struggling to get an excel table on this reply so I can show you.
I've just checked my formulas again and all seems to be correct with the way I've written and copied from your advice

 

[tyija1995]

For the two rows of data in your original post I get 0.69 Hours and 1.14 Hours respectively in column I.

Columns E:H should replicate columns A:D in the example given.