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

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:

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

• Welcome. Please read how to and share a test sheet so as you can be easier helped. Jan 19, 2021 at 20:58
• I want to be able to [...] know the total number of "unplanted" days for that bed. You need to create a meaningful "definition" for `unplanted` that not only takes dates into account but also area under cropping. For example, your sample data has Tomatos planted 21 March and harvested 7 June. But Kale is planted after and harvested before Tomatos - so NE1 can support at least two (or more?) crops at any time. There are 254 days between the earliest planting and last harvest, but total days under crop is 578 days. Do you think that there are any days `unplanted` in your sample data? Jan 20, 2021 at 7:53
• In addition to comments by @Tedinoz, there are still unknowns given your sheet. How long is the full left-to-right window of time? Your chart starts at "50 days after Jan 1"; so is the time to start marking "emptiness" Jan 1? 50 days later? The date of first use? And your chart shows through 450 days (from Jan 1). So what is the end date to stop counting "emptiness"? 450 days from Jan 1 of the previous year? December 31 of current year? Last harvest date? Keep in mind that, while this may all make sense to you, you'll have to be ultra-specific here if anyone is to offer effective help. Jan 20, 2021 at 13:51
• In addition to my above questions, when you manually figure what you currently have on the sheet, what is the total number of "empty time" YOU arrive at? (For instance, if I count January 1, 2021 as the date to start counting usage and December 31, 2021 as the date to stop counting usage, I arrive at 147 "empty" days.) Jan 20, 2021 at 13:53
• @ErikTyler I clarified the timeframe in the question above. It is meant to be the difference between the final harvest date (MAX) and the first planting date (MIN) across all beds. Jan 20, 2021 at 19:50

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". 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() {

// 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 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 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
//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;});//[[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
// 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]+", crop:"+plantingdata[i]+", seed date:"+plantingdata[i]+", trans data:"+plantingdata[i]+", harvest date:"+plantingdata[i]);

if (plantingdata[i] == Cdata[b]){
cropline.push(plantingdata[i]); // bed
cropline.push(plantingdata[i]); // crop
cropline.push(plantingdata[i]); // seed date
cropline.push(plantingdata[i]); // trans date
cropline.push(plantingdata[i]); // 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: 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 