yogi_Compute Elapsed Time Row By Row For Given From And To Times In Hours Minutes And AM_PM

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #648   Jul 22, 2012     www.energyefficientbuild.com.


user wal1006 said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/_is7D5_F5Eg)
Issue converting time from 12 hour into 24 hour format 
I am working on a spreadsheet to process form data.

The form asks users to enter a start time and an end time. In order to make sure that the users are not confused by the 24 hour format, my form asks for Start Time Hour (1-12), Start Time Minutes (15-minute increments), and AM/PM. The same for End Time.

In order to calculate the time difference, I first convert both times to 24 hour format. This is where I have the problem, because when I convert 12:00 pm to 24 hour format, the result is 24:00:00, and 12:00 am converts to 12:00:00:

A1: Start Time Hour

A2: 10

B1: Start Time Minutes

B2: 0

C1: am/pm

C2: am

D1: Start Time 24hr

D2: 10:00:00 using =ARRAYFORMULA((TIME(A2:A,B2:B,00))+((C2:C)="pm")/2)

E1: End Time Hour

E2: 12

F1: End Time Minutes

F2: 0

G1: am/pm

G2: pm

H1: End Time 24hr

H2: 24:00:00 using =ARRAYFORMULA((TIME(E2:E,F2:F,00))+((G2:G)="pm")/2)

I1: Total Time

I2: 14.00 using =ARRAYFORMULA((H2:H-D2:D)*24)

What am I doing wrong in H2?

Thanks for your help!

-----------------------------------------------------------------------------------------------

following is a solution to the problem