Formula About Manhours

[nazarene]

Good day
Can you please help me to make a formula related as per below table, now I am manually doing the process,
the logic is like this, I would like to get only the hours spent from the range of 9:00pm to 6:00am.

For example:
In item # 1- The workers starts to work from 6:27am until 10:09pm so the 9pm to 10:09pm equal hours spent will be 1 hours and 9 minutes
In item # 2 - The Workers starts to work from 4:15am until 12:17pm so the 4:15am to 6:00am equal hours spent will be 1 hours and 45 minutes

Thank you and best regards

 

[jorismoerings]

Hi,

take a look at this post which handles and solves the same question Calculating Minutes between dates

 

[nazarene]

Thank you for the link thread sir, but i have difficulty to apply such example to my file ,
can you please give me a sample formula as per table i showed to you

 

[jorismoerings]

Hi,

Take a look at this:

networkdays.intl.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item	Date	Start Time	Date	Finish Time			exclude	06:00:00	21:00:00
2	1	26-6-2021	06:27:46	26-6-2021	22:09:59	01:09:59
3	2	27-7-2021	04:15:47	27-7-2021	12:17:25	01:44:13
4	3	27-7-2021	03:45:20	27-7-2021	13:28:15	02:14:40
5	4	26-6-2021	06:27:39	26-6-2021	22:09:53	01:09:53
6	5	27-7-2021	03:54:20	27-7-2021	12:05:42	02:05:40
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=(((B2+IF(E2<C2,$J$2,E2))-(B2+C2)+((D2+IF(E2<C2,$J$2,E2))<(B2+C2))))-IFERROR(((NETWORKDAYS.INTL((B2+C2),(B2+(E2<C2)+E2),"0000000")-1)*($J$1-$I$1)+IF(NETWORKDAYS.INTL((IF(E2<C2,B2+1,B2)+E2),(IF(E2<C2,B2+1,B2)+E2),"0000000"),MEDIAN(MOD((IF(E2<C2,B2+1,B2)+E2),1),$J$1,$I$1),$J$1)-MEDIAN(NETWORKDAYS.INTL((B2+C2),(B2+C2),"0000000")*MOD((B2+C2),1),$J$1,$I$1)),0)

 

[nazarene]

Thank you for your help sir, It works perfectly.

Thank you and god bless.

 

[jorismoerings]

A small correction:

networkdays.intl.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item	Date	Start Time	Date	Finish Time			exclude	06:00:00	21:00:00
2	1	26-6-2021	06:27:46	26-6-2021	22:09:59	01:09:59
3	2	27-7-2021	04:15:47	27-7-2021	12:17:25	01:44:13
4	3	27-7-2021	03:45:20	27-7-2021	13:28:15	02:14:40
5	4	26-6-2021	06:27:39	26-6-2021	22:09:53	01:09:53
6	5	27-7-2021	03:54:20	27-7-2021	12:05:42	02:05:40
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=(((B2+IF(E2<C2,$J$2,E2))-(B2+C2)+((D2+IF(E2<C2,$J$2,E2))<(B2+C2))))-((NETWORKDAYS.INTL(B2&C2,D2&E2,"0000000")-1)*($J$1-$I$1)+IF(NETWORKDAYS.INTL(D2&E2,D2&E2,"0000000"),MEDIAN(MOD(D2&E2,1),$J$1,$I$1),$J$1)-MEDIAN(NETWORKDAYS.INTL(B2&C2,B2&C2,"0000000")*MOD(B2&C2,1),$J$1,$I$1))