You want to get a value from a cell and determine the "nearest Friday". "Nearest Friday" is an ambiguous term, so this answer finds the previous Friday as well as the "next" Friday.
The answer is an implementation of How to get the date of every upcoming Friday in mm.dd.yy format in Google App Script? by @Brenden
function findFriday() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
var range = sheet.getRange("A2")
var cellValue = range.getValue()
var dayNumber = new Date(cellValue).getDay()
// object list of the days of the week and respective day numbers
const days = {
sunday: 0,
monday: 1,
tuesday: 2,
wednesday: 3,
thursday: 4,
friday: 5,
saturday: 6
};
// get the day of the cell value
// h/t so_9907419
var key = Object.keys(days).find(k=>days[k]===dayNumber);
// Logger.log("DEBUG: Cell date: "+new Date(cellValue)+", day number = "+dayNumber+", day of the week = "+key)
// h/t so_63854571
// find the date of next Friday
const nextFriday = (cellValue) => findNextFriday(cellValue, days.friday);
var nextValues = nextFriday(cellValue)
var nextTime = nextValues[0]
var nextDaysOffset = nextValues[1]
// Logger.log("DEBUG: next Friday: time = "+nextValues[0]+", days offset = "+nextDaysOffset)
// convert time to date
// h/t so_53833942
var dateObj = new Date(nextTime);
var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
var nextFriFormattedDate = Utilities.formatDate(dateObj, tz, "dd-MM-yyyy");
// Logger.log("DEBUG: Date NEXT friday = "+nextFriFormattedDate)
// find the date for last Friday
// adapted from so_53833942
const prevFriday = (cellValue) => findPrevFriday(cellValue, days.friday);
var prevValues = prevFriday(cellValue)
var prevTime = prevValues[0]
var prevDaysOffset = prevValues[1]
// Logger.log("DEBUG: previous Friday: time = "+prevValues[0]+", days offset = "+prevDaysOffset)
var dateObj = new Date(prevTime);
var prevFriFormattedDate = Utilities.formatDate(dateObj, tz, "dd-MM-yyyy");
// Logger.log("DEBUG: Date LAST friday = "+prevFriFormattedDate)
// update spreadsheet values
var saveValues = []
saveValues.push([dayNumber,key,nextDaysOffset,nextFriFormattedDate,prevDaysOffset,prevFriFormattedDate])
sheet.getRange(2,3,saveValues.length,saveValues[0].length).setValues(saveValues)
}
// h/t so_63854571
// forumla to find "next Friday"
function findNextFriday(date,day) {
// h/t so_4467539
// Javascript modulo formula
Number.prototype.mod = function(n) {
return ((this%n)+n)%n;
}
const nextResult = new Date(date);
// Logger.log("DEBUG: nextDay: result = "+nextResult)
const nextOffset = (((day + 6) - date.getDay()).mod(7))+1
// Logger.log("DEBUG: nextDay: offset = "+nextOffset)
var nextOutcome = nextResult.setDate(date.getDate() + nextOffset);
// Logger.log("DEBUG: nextDay: outcome = "+nextOutcome)
var next = []
next.push(nextOutcome,nextOffset)
return next;
}
function findPrevFriday(date,day) {
Number.prototype.mod = function(n) {
return ((this%n)+n)%n;
}
const prevResult = new Date(date);
// Logger.log("DEBUG: prevDay: result = "+prevResult)
const prevOffset = ((date.getDay()-day).mod(7))
// Logger.log("DEBUG: prevDay: offset = "+prevOffset)
const prevOutcome = prevResult.setDate(date.getDate() - prevOffset);
// Logger.log("DEBUG: prevDay: outcome = "+prevOutcome)
var prev = []
prev.push(prevOutcome,prevOffset)
return prev;
}
Sample
