=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
Title | # Comments | Views | Activity |
---|---|---|---|
Generate a certain future date depending on current cell date. | 15 | 30 | |
Excel IF formula | 3 | 21 | |
Delete all empty columns using VBA | 7 | 42 | |
Data Copy | 4 | 28 |
Join the community of 500,000 technology professionals and ask your questions.