1

I am trying to pull specific data from a timestamp that I will be using in several pivot tables.

Here is my workbook https://docs.google.com/spreadsheets/d/1iYHYdATK06Wxl-Jtxe2P92Kh50XEsWgwx5d-5gib1io/edit#gid=1144545800

To summarize: I have several other workbooks that have a google form attached to them. When my employees fill out the form a timestamp appears in one of the workbooks. The data in those workbooks are being pulled by the link I shared. (i included some random data to play with)

Then I made a pivot table for each Hour of the workday (7am-6pm) to summarize all the information for a specific date and a specific range of time. If I have all the data it's easy for me to filter the pivot table by date and time. See below (In this example I'm retrieving all the data by each processor who worked on 6/25/2019 at 8:00 am to 8:59 am):

Please see image below

This is perfect; however, it only works if I already have the data. When a new day begins and new data is being pulled the pivot table won't pull anything because the filter for a new date and specific hour of the day has not been created. This is because the data doesn't exist yet. Fair enough.

What I want to do is find a way to pull data from a specific future date and specific future time to see live data. For example:

7/01/2019 8:00am to 8:59am, 
7/01/2019 9:00am to 9:59am, 
7/01/2019 10:00am to 10:59am.

So let's say tomorrow (7/01/2019), Max fills out the form at 8:33 am the formula will automatically pull that criteria because it falls between 7/01/2019 8:00 am to 8:59 am

I know I can put formulas into a pivot table or search between different numbers but I am having a really hard time figuring this out. Should I be using something else that's not a pivot table? I would need to SUM the "# OF LOADS" and the "# OF PVS" and the TOTAL by an employee, and by how much they've done an hour. Any help is appreciated. Thank you.

0

You have array mismatch in formula at A598 cell. It should be

=QUERY(IMPORTRANGE("1iYHYdATK06Wxl-Jtxe2P92Kh50XEsWgwx5d-5gib1io",
 "FORM RESPONSE GO TO FORM--> LIVE FORM!A2:G50"), 
 "select Col1,Col2,Col3,Col4,Col5,' ',Col6 label ' '''", 0)

Also, delete the whole column H and paste this into DUMP!H1:

=ARRAYFORMULA({"TOTAL";""; IF(LEN(A3:A), C3:C+E3:E, )})

And then instead of buggy pivot tables you can use query for each hour:

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
  where Col1 is not null 
    and Col9=8 
  group by Col7 
  label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))

...for timestamps with 9th hour:

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
  where Col1 is not null 
    and Col9=9 
  group by Col7 
  label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))

...etc

0

  • USER0 Thank you! Is there anyway to filter by DATE and TIME. Am i limited to only one option? – Steven Lipton Jun 30 at 21:03
  • yes you can: i.stack.imgur.com/pkkzZ.png – user0 Jun 30 at 21:15
  • @StevenLipton webapps.stackexchange.com/help/someone-answers – user0 Jun 30 at 22:05
  • i noticed that the formula returns data not just by he hour but by the minutes and seconds as well. for example: 6/25/2019 8:28:18 and 6/25/2019 10:28:23 would retrieve both timestamps for the hour of 8:00am even though theyre different hours. :( – Steven Lipton Jun 30 at 23:07
  • I had a look and that's not the case. in the formula inside QUERY there is Col9=8 which determines hours. you can test this if you paste =ArrayFormula(HOUR(A3:A)) for example in S3 cell. column S gets populated with hours only. you can check it aganist column A timestamps - i.stack.imgur.com/c4E1D.png – user0 Jun 30 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.