# Need to change the Shift time

#### sksanjeev786

##### Board Regular
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
ABCD
7ProjectProject
8Sent DateDue date
9Same DayTue 6/23/2020 19:00Tue 6/23/2020 22:363.36
Sheet5
Cell Formulas
RangeFormula
D9D9=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

##### Well-known Member
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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### sksanjeev786

##### Board Regular
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

You're Welcome.

Replies
5
Views
96
Replies
11
Views
187
Replies
0
Views
318
Replies
9
Views
200
Replies
5
Views
60