Google Sheet Query output for difference in succesive values in a date column

1
votes

I have a google sheet holding dates(mm/dd/yyyy) in a column. I want to calculate the difference between two successive values(input sheet) in the date column and return the output to a seperate sheet (summary sheet) when there is a difference of more than 1. Trying to use google sheet query since it has the property of executing a query result and automatically add rows based on the retrieval. The first sheet(input) is mostly a dataentry sheet where dates are being entered as an when a process is progressed. So whenever two consequtive date entries differ by more than 1, I want to report the start_date(i.e date value in ith row) and the end_date(i.e., date value i+1th) row. For each i and i+1 th row to be a new row in the summary sheet agaist column start_date(ith value) and end_date(i+1th value). Is there any way we can achieve this is google sheets?

Updated with some reference data:

https://docs.google.com/spreadsheets/d/1-PAwtw07LxH8OYJ9wvPdivcoLHjz1IRHBrqOieqixY8/edit?usp=sharing

1
google-sheetsgoogle-sheets-formulagoogle-sheets-query
share a copy of your sheet with example of desired result – player0
I have updated the question with an example sheet. – Arunavo
The main point here is that the summary sheet to automatically add new rows when there is a new entry in the dataentry sheet which matches the date difference criteria. Dragging the summary sheet to calculate the result for newer rows in the dataentry sheet may not be the greatest option. That's why usage of google query would be a preferable choice. – Arunavo

1 Answers

2
votes

I think it's easier to do the subtraction outside the query (because Query doesn't allow arithmetic on dates) like this:

=ArrayFormula(query({{A2:A},{A3:A;""},{A3:A;""}-{A2:A}},"select Col1,Col2 where Col3>1 label Col1 'Start Date',Col2 'End Date'"))

enter image description here

EDIT

You probably ought to check for any empty cells between dates, so you could add another condition to the Where clause:

=ArrayFormula(query({{A2:A},{A3:A;""},{A3:A;""}-{A2:A}},"select Col1,Col2 where Col3>1 and Col1 is not null label Col1 'Start Date',Col2 'End Date'"))