0

I have a schedule like this and somehow determined the position of Alice in B7.

Given the index B7, how can I find the matching date? Here it would the first cell above Alice that starts with October.

John Smith
  • 1,059
  • 1
  • 13
  • 35

1 Answers1

1

SUGGESTION

It looks it would be quite tricky to use just solely the pre-defined functions to achieve this, given that the total number of names per dates are random (some dates has only 2 names and other has 3 based on your sample sheet).

You can try using a custom formula for this method, just copy and paste the sample functions below as a bound script to your sheet file then adjust it your setup:

function FINDDATE(cell) {
  var sheet;
  if(cell.split("!").length==1){
    sheet = SpreadsheetApp.getActive().getActiveSheet();
  }else{
    sheet = SpreadsheetApp.getActive().getSheetByName(cell.split("!")[0]);
  }
  var selectedLoc = sheet.getRange(cell);
  for(i=selectedLoc.getRow();  i != 1; i--){
    var dateFound = isValidDate(sheet.getRange(i,selectedLoc.getColumn()).getValue());
    if(dateFound == true){ //Return the date of the very first detected as date above the index
      var res = Utilities.formatDate(sheet.getRange(i,selectedLoc.getColumn()).getValue(), Session.getScriptTimeZone(), "MMMM d");
      return res.toString();
    }
  }
}

function isValidDate(d) { //Check if a cell is a date, reference from https://stackoverflow.com/a/17110249
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

SAMPLE DEMONSTRATION:

Two methods below on how to use the FINDDATE custom function:

Method 1:

enter image description here

Method 2:

enter image description here

Method 3: Using FINDDATE on a different sheet

enter image description here

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • Great, that looks like it could work for my case. I'm however working in a different sheet (didn't think this matters) and am not familiar with that scripting language - would it be easy to adapt such that I could use it in another sheet and e.g. call `=FINDDATE("Schedule!B7")`. – John Smith Sep 14 '21 at 12:58
  • Awesome. I just updated my answer and it will now support declaring a specific sheet name like `=FINDDATE("Schedule!B7")`. I have shown a sample image for your reference on my answer. – SputnikDrunk2 Sep 14 '21 at 14:58
  • That did it! Thanks so much! – John Smith Sep 14 '21 at 15:07