Looking for a smooth way to provide completion time in Excel

Question

I have a cell I4 which stores Date and cell I5 which stores time. Based on values in those cells is there a way to provide a Completion Time through either VBA or formula in another cell G6 ?

Completion time is 4 hours.

The problem I'm facing is if completion time is exceeding 17:00 then the stopwatch should be stopped and resume counting from 9:00 following day:

Example

I4 = 09/03/2022
I5 = 15:15
G6 - Completion Time should equal to 11:15 10/03/2022

Any suggestion would be greatly appreciated

Thanks

Answer 1

So, you can work with this:

All the cells are formatted as time.

Answer 2

A neat way of doing this could potentially be using REDUCE():

Formula in K4:

=REDUCE(I$4+I$5,SEQUENCE(I7*1440,,,0)/1440,LAMBDA(a,b,IF((a+b)>(INT(a)+TIME(17,,)),a+TIME(16,1,),a+b)))

As per the screenshot you can see this will now work for any completion time you have in the [h]:mm format.