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
 

Some videos you may like

Excel Facts

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
847
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Nov 11, 2020
Messages
213
Office Version
  1. 2007
Platform
  1. Windows
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
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
200
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

maabadi

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
847
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

1234.jpg
 

sksanjeev786

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


Thanks.
Regards,
Sanjeev
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
847
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Aug 5, 2020
Messages
200
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,076
Messages
5,570,070
Members
412,310
Latest member
mark884
Top