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]

Take it easy. Because both times are in one day, use only:
=C9-B9
And if your shift changed every each week (or 15 days or month) Use this formula for both:
=IF(DAY(B9)=DAY(C9),C9-B9,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))

 

[EXCEL MAX]

If you want to calculate difference between few days and display as number of days value, why you don't use math.

=(C9-B9)*86400/3600

 

[EXCEL MAX]

Or simply =(C9-B9)*24

 

[sksanjeev786]

Take it easy. Because both times are in one day, use only:
=C9-B9
And if your shift changed every each week (or 15 days or month) Use this formula for both:
=IF(DAY(B9)=DAY(C9),C9-B9,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))

Hi Maabadi,

Thanks for your help I have checked with you file for the same day I am getting correct data but for the next day or excluding Sat and Sunday I am not getting exact data but in my formula what I have provided in that it is working for next day and excluding weekend between 19:00 to 4:00 AM but now in 9 AM to 6 PM it is coming 9 hours please see below example.

as my office hour is 9 AM to 6 PM(so after 6 is hour will be null and will start from next day 9 AM) it should get 2 hour instead of 9 hour

MR Excel_1016.xlsx
	A	B	C	D	E	F
1	sat and sun exclude	Fri 11/13/2020 17:00	Mon 11/16/2020 10:00	9.00		2 hours
2	Next day	Thu 11/12/2020 17:00	Fri 11/13/2020 9:00	9.00		2 hours
Sheet4

Cell Formulas
Range	Formula
D1:D2	D1	=IF(DAY(B1)=DAY(C1),C1-B1,IF(INT(B1-TIME(4,0,1))=INT(C1-TIME(4,0,1)),MIN(TIME(23,59,59),MAX(TIME(14,59,59),MOD(C1-TIME(4,0,1),1)))-MAX(MOD(B1-TIME(4,0,1),1),TIME(14,59,59)),TIME(23,59,59)-MAX(MOD(B1-TIME(4,0,1),1),TIME(14,59,59))+MAX(MOD(C1-TIME(4,0,1),1),TIME(14,59,59))-TIME(14,59,59))+MAX(0,(NETWORKDAYS(INT(B1-TIME(4,0,1)),INT(C1-TIME(4,0,1)))-2))*TIME(9,0,0))

Thanks.
Sanjeev

 

[maabadi]

Change all time(4,0,1) in last formula to time(18,0,1).

And I think F2 sould be 1 hour. (why 2 hours?)

Also you can use this formula:
=IF(DAYS(C2,B2)=0,C2-B2,TIME(18,0,0)-B2+C2-TIME(9,0,0))

 

[maabadi]

I modify last formula to this to work properly at all times:
=IF(DAYS(C2,B2)=0,C2-B2,IF(NETWORKDAYS(B2,C2)=0,0,IF(NETWORKDAYS(B2,B2)=0,MOD(C2,1)-TIME(9,0,0),IF(NETWORKDAYS(C2,C2)=0,TIME(18,0,0)-MOD(B2,1),TIME(18,0,0)-MOD(B2,1)+MOD(C2,1)-TIME(9,0,0)))))

And if you want see your hours exactly change your formula column number format to Custom and write at type section :
[h]: mm

 

[sksanjeev786]

I modify last formula to this to work properly at all times:
=IF(DAYS(C2,B2)=0,C2-B2,IF(NETWORKDAYS(B2,C2)=0,0,IF(NETWORKDAYS(B2,B2)=0,MOD(C2,1)-TIME(9,0,0),IF(NETWORKDAYS(C2,C2)=0,TIME(18,0,0)-MOD(B2,1),TIME(18,0,0)-MOD(B2,1)+MOD(C2,1)-TIME(9,0,0)))))

And if you want see your hours exactly change your formula column number format to Custom and write at type section :
[h]: mm

View attachment 26151

Hi Maabadi,

Thank you so much for your hard work on this

I have used your formula and look great for me but If I gap of more then 2 day still I am getting 18:00 instead of 27:00 below is the example for reff,

book1
	A	B	C	D	E	F
1	Same day	Mon 11/16/2020 9:00	Mon 11/16/2020 18:00	9.00		9
2	Next day	Mon 11/16/2020 9:00	Tue 11/17/2020 18:00	18.00		18
3	Day after tommorow	Mon 11/16/2020 9:00	Wed 11/18/2020 18:00	18.00		27
4	hour should calculate between 9:00- 18:00 only	Mon 11/16/2020 9:00	Mon 11/16/2020 20:00	11.00	As my logout time is 18:00	9
Sheet1

Cell Formulas
Range	Formula
D1:D4	D1	=IF(DAYS(C1,B1)=0,C1-B1,IF(NETWORKDAYS(B1,C1)=0,0,IF(NETWORKDAYS(B1,B1)=0,MOD(C1,1)-TIME(9,0,0),IF(NETWORKDAYS(C1,C1)=0,TIME(18,0,0)-MOD(B1,1),TIME(18,0,0)-MOD(B1,1)+MOD(C1,1)-TIME(9,0,0)))))

Also, I have used this formula also getting the same issue after 2 days. (18:00)

Thanks in advance.

Thanks.
Regards,
Sanjeev

 

[maabadi]

I don't think you can work 3 days without rest!!!! Sorry Jocking.
This is Formula:
=IF(DAYS(C1,B1)=0,C1-B1,IF(NETWORKDAYS(B1,C1)=0,0,IF(NETWORKDAYS(B1,B1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))+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)-MOD(B1,1),MOD(C1,1)-MOD(B1,1)+(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))))))

 

[sksanjeev786]

I don't think you can work 3 days without rest!!!! Sorry Jocking.
This is Formula:
=IF(DAYS(C1,B1)=0,C1-B1,IF(NETWORKDAYS(B1,C1)=0,0,IF(NETWORKDAYS(B1,B1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))+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)-MOD(B1,1),MOD(C1,1)-MOD(B1,1)+(NETWORKDAYS(B1,C1)-1)*(TIME(18,0,0)-TIME(9,0,0))))))

Hi Maabadi,

hahaha. it like project TAT we may get time for 1 week also some time :P

Well, I have checked with the above formula still getting diff hours now see the below image for reff.

Same day	Mon 11/16/2020 9:00​	Mon 11/16/2020 18:00​	9:00​		9​
Next day	Mon 11/16/2020 9:00​	Tue 11/17/2020 18:00​	18:00​		18​
Day after tommorow	Mon 11/16/2020 9:00​	Wed 11/18/2020 18:00​	3:00​		27​
hour should calculate between 9:00- 18:00 only	Mon 11/16/2020 9:00​	Mon 11/16/2020 20:00​	11:00​	As my logout time is 18:00	9​

Regards,
Sanjeev