With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Sunday, July 22, 2012
yogi_Compute Elapsed Time Row By Row For Given From And To Times In Hours Minutes And AM_PM
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
B1: Start Time Minutes
D1: Start Time 24hr
D2: 10:00:00 using =ARRAYFORMULA((TIME(A2:A,B2:B,00))+((C2:C)="pm")/2)
E1: End Time Hour
F1: End Time Minutes
H1: End Time 24hr
H2: 24:00:00 using =ARRAYFORMULA((TIME(E2:E,F2:F,00))+((G2:G)="pm")/2)