How to count groups of non-blank cells and blank cells

Question

I am creating a calendar/tracker where a user inputs values into the "Work" column and the spreadsheet should essentially count those cells and the blank cells afterward in order to output values into the "Start Day, End Day, Work Length, and Cycle Length" columns. I'm looking for formulas that can calculate each value in these 4 columns.

Spreadsheet Image

• Start Day (Green) corresponds with the first non-blank cell in the column.
• End Day (Red) corresponds with the last non-blank cell after the Start Day.
• Work Length (Blue) should count from the Start Day to the End Day.
• Cycle Length (Purple, Orange, Yellow, Pink) is the length of days from the start day to the day before the start day of the next month.

My formulas only work in certain scenarios, like if there is only 1 group of values in a column (January and February). They doesn't work for when there are 2 groups of values in a month (March) or when one group starts at the end of the month and continues into the beginning of the next month (March/April).

The April values should be:

• Start Day: Apr 24
• End Day: Apr 27
• Work Length: 4 Days
• Cycle Length: *This can't be determined unless there was a May month (User will input this cycle length)

Spreadsheet link with current formulas: https://docs.google.com/spreadsheets/d/1WceKBNbyrb2rSCiIfVVpdt9l1fKIpPkqqxPataNHgoY/edit?usp=sharing

Answer 1

Alternative Method

You can also try using a Google Sheet Custom Function formula made possible by the Sheet's built-in scripting via Apps Script for a cleaner usage of the sheet formula. Here are the details:

CUSTOM_FUNC(month,array)

• month will be the sheet cell that contains the month name
• The array will be the range that contains the data of every Work columns

This custom formula will return the Start Day, End Day & Work Length automatically. E.g. on your March data, where you have two ranges of Work:

Note: This will also dynamically work on any months & ranges you define. E.g. you can also use =CUSTOM_FUNC(LEFT(H1,3),I3:I33) for the February month on cell J3.

To add the script below as a custom bound script in your spreadsheet file, you may follow the official guide here.

Script

/**
 * Computes the "Start Day", "End Day" & "Work Length".
 *
 * @param {month,array} input The month value, range.
 * @return the "Start Day", "Last Day" & "Work Length".
 * @customfunction
 */
function CUSTOM_FUNC(month,array){
  var out = [];
  for (var arr, i = 0; i < array.length; i++) {
    if (array[i] == "") {
      arr = null;
    } else {
      if (!arr) out.push(arr = []);
      arr.push([array[i],i+1]);
    }
  }
  var res = out.map(x => x.toString().split(","))
  return res.map(y => { return [month+" "+y[1],month+" "+y[y.length-1],(y[y.length-1]-y[1])+1+" days"]});
}

This script was derived from this existing answer to process the range values.

Demonstration

References

• Custom Functions in Google Sheets