Wednesday, March 2, 2011

yogi_TotalHoursByDayFromSortedListOfDateAndTime

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com


Gen 3 Electric said:
I have a form that is giving me date, time and start and stop time. I want to see when the date changes then find the difference from the first time entered from the last time entered for each date. 


Date      Time as   Answer I'm looking for
                 a #

1-1-2011     7.5
1-1-2011     8.5
1-1-2011     11
1-1-2011     16            8.5
1-2-2011     7.5
1-2-2011     12
1-2-2011     16.5          9
1-3-2011     7.5
1-3-2011     11
1-3-2011     13
1-3-2011     14             6.5

--------------------------------------------------
In Sheet1 I used Gen 3 Eletric's source data using the range of values in cells A2:B12 with the following formula in cell C2 ...
=ArrayFormula(if(A2=A3,"",max(if(A$2:A$12=A2,B$2:B$12))-min(if(A$2:A$12=A2,B$2:B$12))))

--------------------------------------------------
Gen 3 Electric commented ...
I'm not sure where I'm where I'm going wrong. I can see how it can work. Thanks for the direction. I tried upping range from A12 to A51. That is because I have a few hundred entries. Could I use A:A?


I added Sheet2 to use open ended ranges A2:A, and B2 using the following formula in cell C2 ...
=ArrayFormula(if(A2=A3,"",max(if(A$2:A=A2,B$2:B))-min(if(A$2:A=A2,B$2:B))))