Need to change the Shift time

[sksanjeev786]

Hi Team,

I have a formula for my shift hour from 19:00 to 4:00 AM but now my shift got change to 9:00 AM to 6:00 PM so can any help me to tweak in below formula as per new time 9AM to 6:00 PM.

FS1.xlsx
	A	B	C	D
7		Project	Project
8		Sent Date	Due date
9	Same Day	Tue 6/23/2020 19:00	Tue 6/23/2020 22:36	3.36
Sheet5

Cell Formulas
Range	Formula
D9	D9	=IF(INT(B9-TIME(4,0,1))=INT(C9-TIME(4,0,1)),MIN(TIME(23,59,59),MAX(TIME(14,59,59),MOD(C9-TIME(4,0,1),1)))-MAX(MOD(B9-TIME(4,0,1),1),TIME(14,59,59)),TIME(23,59,59)-MAX(MOD(B9-TIME(4,0,1),1),TIME(14,59,59))+MAX(MOD(C9-TIME(4,0,1),1),TIME(14,59,59))-TIME(14,59,59))+MAX(0,(NETWORKDAYS(INT(B9-TIME(4,0,1)),INT(C9-TIME(4,0,1)))-2))*TIME(9,0,0)

Thanks.
Sanjeev

 

[maabadi]

This is completed version:
=IF(NETWORKDAYS(B1,C1)=0,0,IF(NETWORKDAYS(B1,B1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))+MIN(0.75,MOD(C1,1))-TIME(9,0,0),IF(NETWORKDAYS(C1,C1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))+TIME(18,0,0)-MAX(0.375,MOD(B1,1)),MIN(0.75,MOD(C1,1))-MAX(0.375,MOD(B1,1))+(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0)))))

Are You format Result column totally to [h]:mm. this see because format is time not modified version.

 

[sksanjeev786]

This is completed version:
=IF(NETWORKDAYS(B1,C1)=0,0,IF(NETWORKDAYS(B1,B1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))+MIN(0.75,MOD(C1,1))-TIME(9,0,0),IF(NETWORKDAYS(C1,C1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))+TIME(18,0,0)-MAX(0.375,MOD(B1,1)),MIN(0.75,MOD(C1,1))-MAX(0.375,MOD(B1,1))+(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0)))))

Are You format Result column totally to [h]:mm. this see because format is time not modified version.

ops....!!!

Thanks for checking it out.

Awesome work!!!

Thank you once again for your hard work in this.

Regards,
Sanjeev

 

[maabadi]

You're Welcome.