Working hrs. Minus breaktime (8hrs/day) from range of days

[PrinceExcel]

Work Start: 8:00 am
Work End :5:00 pm
Break Start: 12:00 pm
Break End:1:00pm


I am trying to compute the working hours with raw data like 08/30/2019 4:00pm - 09/02/2019 9:00am. The result in this one should be 2 hrs only. But if the raw data is 09/02/2019 8:00 am - 09/02/2019 9:00am result shpuld be 1 hr. Also ex. 09/02/2019 12:00pm - 2:00pm should be 1 hr since 12-1pm is break time. Basically I am trying to compute working hrs. from range of days subtracting break time and 8 hrs work per day only.

Please help.

 

[jorismoerings]

Hi,

Approach: divide working day in 2 shifts. First shift runs from 8 till 12 hrs, second shift runs from 13 till 17 hrs.
So take a look at this and see if this helps:

Book1
	A	B	C	D	E	F
1	Work Start: 8:00 am			8-12	13-17	total
2	Work End :5:00 pm	30/08/2019 16:00	02/09/2019 09:00	1:00:00	1:00:00	2:00:00
3	Break Start: 12:00 pm	02/09/2019 08:00	02/09/2019 09:00	1:00:00	0:00:00	1:00:00
4	Break End:1:00pm	02/09/2019 12:00	02/09/2019 14:00	0:00:00	1:00:00	1:00:00
Sheet1

Cell Formulas
Range	Formula
D2	=(NETWORKDAYS.INTL(B2,C2,1)-1)*("12:00"-"08:00")+IF(NETWORKDAYS.INTL(C2,C2,1),MEDIAN(MOD(C2,1),"12:00","08:00"),"12:00")-MEDIAN(NETWORKDAYS.INTL(B2,B2,1)*MOD(B2,1),"12:00","08:00")
E2	=(NETWORKDAYS.INTL(B2,C2,1)-1)*("17:00"-"13:00")+IF(NETWORKDAYS.INTL(C2,C2,1),MEDIAN(MOD(C2,1),"17:00","13:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(B2,B2,1)*MOD(B2,1),"17:00","13:00")

 

[PrinceExcel]

I cant see the whole formula do you have the xcel file on this one?please send it to me andrewdiokno91@gmail.com.thank you very much

 

[PrinceExcel]

The formula is cut.Whats next after "08:00?

Thanks

 

[jorismoerings]

Hi,

You can download the file from here

 

[PrinceExcel]

Thank you so much..this formula works. I only need to change the cells formal to [h]:mm for it to counts 8 hrs per day.����