How can gaps be quantified within a combined set of variable arrays?

Question

I'm using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days within a given timeframe that no crops are planted in a bed. In the end, I want to be able to be able to reference a single bed by name within the table (e.g. Bed 1 or NW4) and know the total number of "unplanted" days for that bed.

The user of the spreadsheet determines...

• the number of crops within a bed
• the start date of each crop (planting)
• the finish date of each crop (harvest)

My problem solving attempts have followed these steps without success:

1. Calculate timeframe by subtracting final harvest (MAX) by first planting (MIN) across all beds
2. Filter by bed name (e.g. NE1)
3. Create one or many arrays of dates using the start date and end date for each crop
4. Combine & flatten arrays
5. Count number of unique dates

Since it is possible for dates/arrays to overlap I thought the =UNIQUE(FLATTEN(range) formula would be useful (total days - unique days = unplanted days); however, step 2 is proving to be especially difficult. I can't figure out how to make a combined array that's extrapolated from a variable array. Ya dig?

The images below are only meant to be visual representations of the examples.

Example 1: a single crop (green) is planted leaving a total of 5 days "unplanted" (red).

Example 2: two crops are planted leaving gaps in the middle and the ends.

Example 3: many crops are planted and many overlap leaving gaps in the beginning and middle.

Here's a working model of the crop planning tool but I haven't made any meaningful progress on the question above:

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

BONUS: I'd be curious to see a version that uses "transplant date" as the start date if present.

Answer 1

There are several aspects to your question. This answer is designed to calculate the number of "unplanted days" for a given bed. You want to analyse your date by bed, though your sample date includes a single bed. Extending this answer to consider additional beds, each with their own crop plan is the next step.

The answer depends on two columns; this could probably be simplified but the purpose at this stage is to show a method for calculating the "unplanted" days.

Note: The answer also assumes that the data is sorted by Seed Date.

Latest Harvest Date

• In Cell F2, insert this formula and copy down as many rows as required
• =max($E$2:E2): This will show the latest harvest date.

Unplanted Days

• In cell G3, insert this formula and copy down as many rows as required
• =if(C3<=F2,0,C3-F2): This compares the crop "seed date" with the "latest harvest date" at the time of planting the previous crop. If the seed date greater than the previous "latest Harvest date", then the number of "unplanted days" equals the difference between the "seed date" and the previous "latest harvest date".

---

---

ADDENDUM#1 (23 Jan 2021)

This is a continuation of the earlier answer explaining how to calculate unplanted days.

This includes details for:

• copying "Crop Plan" to another sheet for easier analysis
• creation of help columns
• updating the formulas for "Max Harvest date" and "Unplanted days" to reflect a data source that includes multiple crop beds.
• conditional formatting

Copying "Crop Plan" to another sheet

A farmer may choose many beds and many crops, many dates. The data entry screen on Crop Plan is convenient for entering small volumes, but it doesn't lend itself to entering volumes of data, or to analysing data.

I propose that "Crop Plan" data is copied/appended to a second sheet. I called this sheet "Output" but the sheet name is a variable in the code, so you can change this to whatever you might want.

The script to copy "Crop Plan" to "Output" is:

---

function updateCrop2Output() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // define sheeta for Crop Plan and output
  var datasheet = "Crop Plan";
  var sheet = ss.getSheetByName(datasheet);
  //Logger.log("DEBUG: the sheet = "+sheet.getName())
  var outputsheet = "Output"
  var output = ss.getSheetByName(outputsheet);

  // define the data and variables
  var headerrow=15; // header row on Crop Plan
  var outputarray = []; // array to hold Crop Plan data for update to Output

  // find the number of records in the Crop Plan data
  // also find the number of beds in the Crop Plan data
  var CLR = sheet.getLastRow();
  var Crange = sheet.getRange(headerrow+1,3,CLR);
  var Cvals = Crange.getValues();
  var Clast = Cvals.filter(String).length;
  //Logger.log("DEBUG: Number of rows of data = "+Clast);


  // get only the actual rows in the Bed Column (Column C) with data
  var newCvals = sheet.getRange(headerrow+1,3,Clast).getValues();
  //Logger.log("this is newCvals");
  //Logger.log(newCvals)
  // convert the format of the 2D data to 1D
  var Cdata = newCvals.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
  //Logger.log("DEBUG: this is c data");
  //Logger.log(Cdata); // DEBUG;


  // get the unique values
  Cdata = Array.from(new Set(Cdata));
  // Logger.log("DEBUG: this is the unique values in CData");
  // Logger.log(Cdata); //DEBUG
  // Logger.log("DEBUG: number of unique beds = "+Cdata.length);
  for (var cd = 0;cd < Cdata.length;cd++){
    // Logger.log("cd="+cd+", cdata = "+Cdata[cd])
  }
  

  // get all the CROP data
  var plantingrange = sheet.getRange(headerrow+1,3,Clast,8);
  // Logger.log("DEBUG: plantingrange = "+plantingrange.getA1Notation());
  var plantingdata = plantingrange.getValues();


  for (b=0;b<Cdata.length;b++){
    // Logger.log("DEBUG: Cdata: Bed:"+Cdata[b])
    for (i=0;i<Clast;i++){
      var cropline = [];
      //Logger.log("DEBUG: Planting data: Bed:"+plantingdata[i][0]+", crop:"+plantingdata[i][1]+", seed date:"+plantingdata[i][3]+", trans data:"+plantingdata[i][5]+", harvest date:"+plantingdata[i][7]);
     
      if (plantingdata[i][0] == Cdata[b]){
        cropline.push(plantingdata[i][0]); // bed
        cropline.push(plantingdata[i][1]); // crop
        cropline.push(plantingdata[i][3]); // seed date
        cropline.push(plantingdata[i][5]); // trans date
        cropline.push(plantingdata[i][7]); // harvest date
        outputarray.push(cropline); // push the whole row onto the outout array
      }
    }
  }

  // get the number of rows in outputarray
  var outputCount = outputarray.length;

  // define the output range 
  var outputLR = output.getLastRow();

  // define range on output
  var outputCount = outputarray.length;
  output.getRange(outputLR+1,1,outputCount,5).setValues(outputarray)
  // Logger.log("DEBUG: Crop Plan copied to Outputsheet")

}

---

Assuming that the header row is already in place, the screen would look like this:

---

Helper Columns

The answer uses helper columns on sheet "Output". These describe:

• the unique crop beds

• the first row on which the crop bed appears.

• the last row of data

• Cell I2: =unique(INDIRECT("A2:A"&ArrayFormula(max(if(len(A:A),row(A:A),)))))

  • a dynamic formula that detects the last row, and lists the crops accordingly.

• Cell J2: =MATCH(I2,INDIRECT("$A$2:$A$"&$L$2),0)+2

  • returns the first row number in which each crop bed appears.
  • copy this formula down as many rows as unique beds.

• Cell L1: =ArrayFormula(max(if(len(A:A),row(A:A),)))

  • this value is used in formulas to reference the end of a range.

Assuming that the header row is already in place, the helper columns would look like this:

---

Revised Formula: "Max Harvest date" and "Unplanted Days"

The formula originally described assumed a single crop bed, but this is an unrealistic expectation in the grand scheme. So these formula have to be responsive to the crop bed.

Max Harvest Date

• Cell F2: =max(INDIRECT("$E$"&vlookup(A2,$I$2:$J$6,2,false)&":E"&row()))
  • Copy this formula down to the last row of data

Unplanted days

• Cell G2: =if(C2<=F1,0,C2-F1)
  • Copy this formula down to the last row of data

Conditional formatting

• If desired, you can highlight the unplanted days. Select the data range, and use this custom formula
  • =and(G2>0,isblank(G2)=FALSE)

---

Output

The final output screen might look like this:

---

Addendum#2 (23 Jan 2021)

DATA ANALYSIS BY CROP BED

The results for a given "Crop Bed" can be easily analysed using a FILTER.

In this example, I have created an additional sheet. The headers have been copied longhand.

Selecting a "Crop Bed"

Refer the attached screen shot.

• Crop Bed selector

  • Cell B1: Data validation from a range =Output!$A$2:$A$1000

• Filter formula

  • Cell A3: =Filter(Output!A2:G, SEARCH( $B$1, Output!A2:A ) )

---

Filtered Data

---