Google-sheets – How to update Google worksheet with data from separate worksheet based on dates

google sheetsgoogle-apps-scriptgoogle-sheets-dates

I have a worksheet of "Loads" that contains a growing list of loads done by drivers. This worksheet grows with new rows daily. I am wanting to have a seperate worksheet called "New Loads" in the same workbook that only contains the rows from "Loads" that falls withing a date range.

For example, I want to pull all the rows from worksheet "Loads" into worksheet "New Loads" when the worksheet "Loads" cell A1 "Delivery date" falls between worksheet "New Loads" cell A1 "start date" and A2 "end date".

I also am wanting any data pulled into the "New Loads" worksheet to overwrite all old date that was previously pulled. So that way the only data in "New Loads" is data that matches the date range.

Best Answer

You want to extract a report from "Loads" based records that fall between a given "start date" and "end date".

There might be several ways to do this. This answer uses the QUERY function to display the record details.

Use this formula:

=query(Loads!A2:C,"select A, B, C where A>=date '"&TEXT(B1,"yyyy-mm-dd")&"' and A<=date '"&TEXT(B2,"yyyy-mm-dd")&"'")

  • The "where" clause tests for a delivery date greater than or equal to the "start date" AND a delivery date that is less than or equal to the "end date".

When dates are used as criteria in a query, there are two issues to consider:

  • the function expects the dates to be strings. So in this answer, the "start date" and "end date" are converted using TEXT()
  • the format of the date string must be "yyyy-mm-dd". This has nothing to do with how the date record is formatted; it's just an issue for the query.

New Loads

newloads

Loads

Loads

Related Topic