yogi_Compute Difference In Hours And Minutes Between Planned And Actual Time Of Operation

Google Spreadsheet   Post  #2356

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-28-2018

question by Bryan Columbus

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/2DYUCgrFP2A;context-place=forum/docs
Help with Google array formula for duration between two times

Help with Formula Google sheets for duration between two times. Array Formula that can:

1. Calculate the duration between times if the End time is after the Start time

2. Calculate the duration between times if the End time is before the Start time

3. Calculate the duration between times if the start time is before midnight and end time is after midnight or vise versa

I tried and searched for hours to have one formula for this. This is as close as I could get so far:

=ARRAYFORMULA(if(len(C2:C)*len(D2:D),D2:D-C2:C,)) 

Results:

     C                   D                          E (Formula)       C and D are time format  E is format [H]:MM

10:00 PM        10:30 PM                    0:30

11:00 PM        10:30 PM                  -0:30

1:30 AM          10:30PM                    21:00

Tried many other formulas, nothing did it yet

Any help would be really appreciated thanks