Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
=StartTime+(Duration/24)+(INT(-Duration/24)/-12)-LOOKUP(StartTime,$F$4:$F$10,$E$4:$E$10)-(LOOKUP(StartTime+(Duration/24)+(INT(-Duration/24)/-12)-LOOKUP(StartTime,$F$4:$F$10,$E$4:$E$10),$B$4:$B$10,$I$4:$I$10))
You will find this formula in the attached workbook Sheet2!K26. The sheet explains it. Refer to the rows 14 to 23 based upon which the formula is developed in row 26 with the final result in K26.=(LOOKUP($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19,INDEX(Shifts,0,5),INDEX(Shifts,0,3)))+($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19)-LOOKUP($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19,INDEX(Shifts,0,5),INDEX(Shifts,0,3))
It is designed for row 19 where the start time and duration are in columns A and B.If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.