# 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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

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

Last edited:

#### EXCEL MAX

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

##### Board Regular
Or simply =(C9-B9)*24

#### sksanjeev786

##### Board Regular

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))

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
ABCDEF
1sat and sun excludeFri 11/13/2020 17:00Mon 11/16/2020 10:009.002 hours
2Next dayThu 11/12/2020 17:00Fri 11/13/2020 9:009.002 hours
Sheet4
Cell Formulas
RangeFormula
D1:D2D1=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

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

Last edited:

##### Well-known Member

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

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

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
ABCDEF
1Same dayMon 11/16/2020 9:00Mon 11/16/2020 18:009.009
2Next dayMon 11/16/2020 9:00Tue 11/17/2020 18:0018.0018
3Day after tommorowMon 11/16/2020 9:00Wed 11/18/2020 18:0018.0027
4hour should calculate between 9:00- 18:00 onlyMon 11/16/2020 9:00Mon 11/16/2020 20:0011.00As my logout time is 18:009
Sheet1
Cell Formulas
RangeFormula
D1:D4D1=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.
Regards,
Sanjeev

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

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

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

Replies
5
Views
92
Replies
11
Views
180
Replies
0
Views
298
Replies
9
Views
177
Replies
5
Views
48