2

I am using Google Forms and Sheets to create a way of tracking services including time for some of our students. I have the forms linked to a Sheet and am able to pull the "Clock In" data and "Clock Out" data into a single sheet. My issue is: when I go to analyze using pivot tables it pulls the data from the cell of the next row (students do not necessarily clock in/out in the order they arrive). Is there a way I can link the data by name and then order by time?

Any help would be greatly appreciated!

Quick View of Issue

Example Sheet

player0
  • 124,011
  • 12
  • 67
  • 124
MB32
  • 33
  • 4
  • 2
    I am sorry I am a new user and I am waiting to get 10 rep before I can share an image! I am trying! – MB32 Apr 25 '19 at 21:04
  • 1
    I was able to share a sample of my sheet. I appreciate any input! Thanks! – MB32 Apr 25 '19 at 21:15

1 Answers1

1

you can but not in one go because pivot tables are not so advanced. create a new sheet and paste this in A1 cell and then crate your pivot table from this sheet:

={QUERY({Combination!A1:E}, "where Col1 is not null order by Col3, toDate(Col1) , toDate(Col2)", 1),
  QUERY({Combination!F1:I}, "where Col1 is not null order by Col3, toDate(Col1) desc, toDate(Col2) desc", 1)}

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you for a link! One more quick question on the QUERY, It seams it orders then by time out before date. IE now I have two posts for Testing 2 and because I clocked out today 4/29 at 8:28:44 it is ordering it before the 4/25/2019 13:14:51. Is there another parameter I can add to the QUERRY that says order by date then time? Again I appreciate your response! – MB32 Apr 29 '19 at 17:14
  • 1
    **toDate** covers the whole timestamp (date + time including). I believe that scenario you describe can be solved if you remove one (or both) **desc** from query fx – player0 Apr 29 '19 at 17:20