0

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).

enter image description here

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

enter image description here

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

enter image description here

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.

8
  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Jan 19 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? – Tedinoz Jan 20 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. – Erik Tyler Jan 20 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.) – Erik Tyler Jan 20 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. – Todd Jones Jan 20 at 19:50
0

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".

screenshot


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:

Output screen


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:

Helper Columns


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:

Final Output


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

Filter


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.