Excel/Google Sheet Adding Duration to Date/Time [duplicate]

Question

I have data relates to time. I have the start date, start time and end time. My objective is to add the end time to the start date/time to return the End date and time.

If the start and end are on the same day no issues. I have multiple ways of calculating the required data. The issue is when the end time is on the next day.

How I tried to tackle it so far

Date Column B Start Column C End Column D

Column G: B+C = Returns Date - Time Start Column H:=IF(C2>D2,SUM(C2-D2)*24,SUM(D2-C2)*24) Returns Duration Column I:=G2+TIME(H2,0,0) End Date and time

I have an inelegant solution of =D12-C12 returns a value of ############ The user then adds '+1' to the formula. But I am trying to avoid user intervention.

Thanks

Answer 1

delete everything in G2:I and use this in G2:

=INDEX(IF(B2:B="",,TEXT(B2:B+C2:C, "d/m/e hh:mm:ss")))

in H2:

=INDEX(IF(B2:B="",,TEXT(I2:I-G2:G, "[h]:mm")))

and in I2:

=INDEX(IF(B2:B="",,TEXT(IF(C2:C<D2:D, B2:B, B2:B+1)+D2:D, "d/m/e hh:mm:ss")))