sksanjeev786
Well-known Member
- Joined
- Aug 5, 2020
- Messages
- 883
- Office Version
- 365
- 2016
- Platform
- 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.
Thanks.
Sanjeev
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