Need to change the Shift time

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
200
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
844
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
200
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,118,075
Messages
5,570,051
Members
412,309
Latest member
essobsan
Top