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
Date Time as Answer I'm looking for
a #
1-1-2011 7.5
1-1-2011 8.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))))
Hi Yogi,
ReplyDeleteThanks 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.
Hi Oggie Sparks:
ReplyDeleteThe 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