Sunday, July 22, 2012

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

user wal1006 said:(!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