Need to change the Shift time

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
201
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,095
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

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

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
201
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,084
Messages
5,576,022
Members
412,694
Latest member
Deaf1Too
Top