Calculating working hours

[aoife]

Completion Date	Completion Time	Reported date	Reported time
01.05.2019	12:07:06	01.05.2019	11:25:27
02.05.2019	15:24:49	02.05.2019	14:16:31

<tbody>
</tbody>

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.

	A	B	C	D	E	F	G	H	I
1	Completed Date	Completed Time	Report Date	Report Time	Imp. Comp Date	Imp. Comp Time	Imp. Report Date	Imp. Report Time	Working Hours
2	05/05/2019	12:07:06	02/05/2019	11:25:27	06/05/2019	08:30:00	02/05/2019	11:25:27	14.08

<tbody>
</tbody>

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.

	A	B	C	D	E	F	G	H	I
1	Completed Date	Completed Time	Report Date	Report Time	Imp. Comp Date	Imp. Comp Time	Imp. Report Date	Imp. Report Time	Working Hours
2	05/05/2019	12:07:06	02/05/2019	11:25:27	06/05/2019	08:30:00	02/05/2019	11:25:27	14.08

<tbody>
</tbody>

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.