1

I'm working on a spreadsheet that records logistics reports via a google form. We have 100 locations and logistics personal visit and report on condition of each location, i.e. the location needs maintenance etc.

We have 3 categories of location: Priority locations that need to be visited daily, secondary locations that need to be visited a minimum every other day, and other locations that we aim to visit a minimum of every 3 days.

Each logistics visit is recorded in the spreadsheet via the form it captures:

Column A = Time Stamp,  
Column B = Location,  
Column C = Damage Yes/No,  
Column D = Damage Description,  
Column E = Personnel ID,  

I want to create a query that shows only the last visit for each location where that date is older than 3 days. If it's been visited to day or yesterday I don't want it to show up, and as we visit each location multiple times I only want to show 1 entry for each location, the latest entry.

This is what I've attempted:

=Query(LogisticsReports, " SELECT A, B, J WHERE A < TODAY()-3 ",1)
1

Short answer

Try

=QUERY(
   LogisticsReports,
   "SELECT A,B,J WHERE A < date '"&TEXT(TODAY()-3,"yyyy-mm-dd")&"'",
   1
)

Explanation

QUERY use Google Visualization API Query Language. It handles dates differently than Google Sheets, so the result of functions that returns a date should be convert to a string having yyyy-mm-dd date format and have to be preceded by the date keyword.

  • That's great! I changed to A> date, that works better for what I want. Now I want to try and group the answers by location. Where would I add GROUP by =QUERY( LogisticsReports, "SELECT A,B,J WHERE A < date '"&TEXT(TODAY()-3,"yyyy-mm-dd") GROUP by B &"'", 1 ) – Malcolm Lesley Oct 4 '16 at 19:33
  • Thinking about it, this will not show locations that have a date older than TODAY()-3. But I want to ensure that the most recent date for each location is visible no matter what even if it's older than TODAY()-3 – Malcolm Lesley Oct 4 '16 at 19:56
  • 1
    Comments are intended for to criticize or to ask clarification regarding a question or answer. Follow-up questions should be posted as new questions. – Rubén Oct 4 '16 at 22:32

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.