输入日期后冻结日期? [英] Freeze a date, once entered?

问题描述

我使用一个用于项目计划的工作表，该工作表的列名为状态"，从下拉菜单中可以选择完成"或打开".在另一个单元格中，我输入我的ECD(预计完成日期).

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).

我想将任务的状态设置为完成"，另一个名为ACD(实际完成日期)的单元格应显示当前日期，并在此之后冻结该单元格.使用功能TODAY或NOW总是会每天更改日期.

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?

推荐答案

我爱您COM增值税！

但是不要成为一个顽皮的男孩，不要分享您的代码...

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

在提交表单或对行A进行编辑后，将A列保留为空，将显示当前日期，并且该日期将不会更改:-)

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());

}

这篇关于输入日期后冻结日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！