Google-sheets – How to make a cell autopopulate the date dependent on another cell

google sheetsgoogle-sheets-datesgoogle-sheets-timestamp

I'm trying to make a recruitment sheet for an online community I help run. What I'm currently attempting to do is that when we record a person has joined the Discord to column B, it'll automatically propagate the date joined in the corresponding space in column D.

I've never messed with date or timestamp functions in excel, so I'm not really sure where to start with this. I tried doing a bit of googling, but all that came up was 'how to create an auto-updating date.'

Here's my Test Sheet.

Best Answer

First, you can use this script to get yourself a timestamp function:

function TIMESTAMP() {

var today = new Date();
var date = (today.getMonth()+1)+'-'+today.getDate()+'-'+today.getFullYear();
var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
var dateTime = date+' '+time;
return dateTime;

}

Then, you should be able to use an if statement to call it into action. For example, "If(c3="","",TIMESTAMP())", then drag that down all the way for the column.

Let me know if that makes sense!

Related Solutions

Google-sheets – Auto date insert when opposite cell is populated

This answer is coming late to the party, but you can achieve what you want by installing this script:

function onEdit(event) {
  var eventRange = event.range;
  if (eventRange.getColumn() == 4) { // 4 == column D
    var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1);
    var values = columnARange.getValues();
    for (var i = 0; i < values.length; i++) {
       values[i][0] = new Date();
    }
    columnARange.setValues(values);
  }
}

EDIT: It seems you just want to trigger the script when column D is modified, so I have modified the code accordingly.

To install it, go to Tools -> Script editor, paste the code above, and save. From then on, every time you edit a cell in column D, the current timestamp will be inserted in column A.

Google-sheets – Google spreadsheet cell outcome dependent on another cell’s range

I believe you are looking for a nested if statement. Put this formula in B1 and then drag it down as far as needed...

=IF(AND(A1>1,A1<10), A1*.75,(IF(AND(A1>10,A1<20), A1*.60, (IF(AND(A1>20,A1<30), A1*.50, (IF(test, then_true, otherwise_value))))))

I just did the first 3 iterations. You can continue from there and set the values as needed. Make sure to match left and right parenthesis.

I hope this helps.

Best Answer

Related Solutions

Google-sheets – Auto date insert when opposite cell is populated

Google-sheets – Google spreadsheet cell outcome dependent on another cell’s range

Related Topic