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))))


2 comments:

  1. Hi Yogi,

    Thanks for that help. It works great!Huge help.

    The A2:A open ended range is new to me. I will add that to my tool box.

    I understand most of the statement but I'm a little unsure of how this formula know how to set the range as all like dates.

    ReplyDelete
  2. Hi Oggie Sparks:

    The formula compares the ActiveCell with the cell below, if the dates are the same, it goes down one cell and repeats the cycle again ... till it finds that the ActiveCell is not equal to the cell below -- this way it knows that the cells above arte the same date as the ActiveCell.

    I know the preceding is a mouthful of a statement, but I hope it makes sense.

    Cheers!
    Yogi

    ReplyDelete