Google-sheets – Is it possible to set dates across a row of cells where each cell date counts back different amounts of weekdays from a completion date

I wrote a custom function in Google Apps Script that takes all the criteria you want it to have (and more) in consideration.

Code

function myPayments(paymentPlan, dateRange) {
  var output = [];  
  for(var i=0, iLen=dateRange.length; i<iLen; i++) {
    var checkDate = new Date(dateRange[i]);
    var weekDay = checkDate.getDay()+1, sum=0;
    for(var j=0, jLen=paymentPlan.length; j<jLen; j++) {
      if(paymentPlan[j][1] == weekDay) {   
        if(paymentPlan[j][2] === "" && paymentPlan[j][3] === "") {
          sum += paymentPlan[j][0];
        } else if(paymentPlan[j][2] === "") {
          if(checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][3] === "") {
          if(checkDate >= new Date(paymentPlan[j][2])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][2] !== "" && paymentPlan[j][3] !== "") {
          if(checkDate >= new Date(paymentPlan[j][2]) && 
            checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } 
      }
    }
    if(sum == 0) {
      output.push([]);
    } else {
      output.push([sum]);
    }
  } 
  return output;
}

Screenshot

Remarks

If you extend the paymentPlan range, to contain the rest of the data as presented in the PAYMENT PLAN sheet, then you will notice that overlapping payments will be summed.

Add the script under Tools>Script editor and press the save button (in your case it's already there).

Example

I've added the solution in your example file.

I would suggest something that might not quite meet "delete the values" etc but might serve as giving the appearance of doing so. Use two sheets, one (say RefData) with dates across say Row1 (say starting with March 17 in B1) and cell values such as Exam in say Row3 of the corresponding column. The intention being to have no need to ever delete the contents of this sheet - merely keep extending it to the right.

The other sheet would be very much like the example provided by @Punchlinern, though maybe with =text(B1,"dddd") in B2 (copied across) since WEEKDAY is not necessary, and in say B4 and copied across:

=offset(RefData!$A$1,2,match(B1,RefData!1:1,0)-1) 

This sheet would provide a view of RefData and be populated exclusively by formulae - all data would be entered in RefData.

The optional fourth parameter for offset might be applied if to show more than just a single cell value per day, perhaps a row for each of morning, lunch, afternoon, dinner, evening.