google apps script - Freeze a date, once entered?

Question

I use a sheet for project planning which has a column called "Status", where I can choose "Done" or "Open" from a pull down menu. In another cell, I enter my ECD (expected completion date).

I would like to set the status of a task to "Done" and another cell called ACD (actual completion date) should show the current date and freeze the cell after that. Using the function TODAY or NOW would always change the date, every day.

Is there a formula or script that could do that for me?

Answer 1

I love you COM VAT!

But don't be a naughty boy and not share your code...

Keep column A empty, upon form submission or an edit on the row column A will show the current date and it WILL NOT CHANGE :-)

function onEdit() {
var dateColNum = 1 //column F
var ss1 = SpreadsheetApp.getActiveSpreadsheet();

//set date in same row as edit happens, at fixed column  
var nextCell = ss1.getActiveSheet().getRange(ss1.getActiveRange().getLastRow(), dateColNum, 1, 1)
if( nextCell.getValue() === '' ) //is empty?
    nextCell.setValue(new Date());

}