Have a spreadsheet (named) where I have a column of dates (B2) and a column of checkboxes (C2). I need to compare B2 to Today() if the date is older than X number of years then C2 needs to be ticked (TRUE); else do nothing.

https://docs.google.com/spreadsheets/d/1_FdOgb0RKqxu6mIggmR2n5JDitpPjxzs1h-fn6bGnHs/edit?usp=sharing

Attempted possible formula's but this seems to be a script function. Attempted a few scripts but no luck.

CodePudding user response：

There is a Google Sheet function that calculates the number of years between two dates and accepts and optional argument depending on how you count the years (it seems a financial counting).

YEARFRAC

The formula in C2 would be:

=arrayformula(if(YEARFRAC(A2,B2:B18,1)>=2,TRUE()))

where you can change the number of years (I wrote "2") but you can reference it to another cell depending if you want to change the number of years.

If you desire to use a script, it could be something like the following:

function dates() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NAME OF YOUR SHEET');
  const initialDate = new Date(ss.getRange('A2').getValue()).getTime();
  const dates = ss.getRange('B2:B18').getValues();
  let results = []
  dates.forEach(date => {
    let newDate = new Date(date).getTime();
    let difference = (initialDate - newDate) / 1000 / 3600 / 24 / 365
    difference >= 2 ? results.push([true]) : results.push([false])
  })
  ss.getRange('C2:C18').setValues(results);
}

.getTime()

CodePudding user response：

You can put your DATEDIF(...)>=2 function directly in a checkbox cell (just overwriting its default FALSE that appears in the formula bar), and it will generate a checkmark if it evaluates to TRUE. Note that the checkbox won't be manually controllable any more (if you click on it, its state will not change).