Freeze a date, once entered?

问题:

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?

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

}

回答2:

Assume the "done" is in cell A1 and date/time in B1

=IF(A1="Done",IF(LEN(B1)>0,B1,NOW()),"")

however, you need to turn on iterations

also if you erase the "Done" the cell will be empty again

回答3:

What you're looking for is a macro, not a formula. This solution assumes your ECD column is 'A' (column 1), then your STATUS column is column 'B' (column 2)

Step 1) Press Alt+F11, this will open up the code editor in VBA.

Step 2) Copy and paste the code below into your 'sheet' that your formula resides in.

Step 3) Save your workbook as a macro-enabled spreadsheet. (xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If (Target.Columns.Count = 1 And Target.Column = 2 And Target.Rows.Count = 1) Then
        'Check every row, currently this will get executed once
        For Each cell In Target.Cells
            'Make sure the value is set to 'done'
            If (UCASE(cell.Value) = "DONE") Then
                'Using the current modified row, set column 1 to Today
                'Note: Now() is the same as Today in VBA
                Target.Worksheet.Cells(cell.row, 1).Value = Now()            
            End If
        Next cell
    End If  
End Sub

Note: This macro only gets executed if only one cell gets set to 'done'. You can change it to work on multiple cells by removing the 'Target.Rows.Count = 1' part of the FIRST 'if' statement. This scenario may happen if you're 'extending' via clicking and dragging the 'done' value to multiple cells.

Note2: If you want to assign the date once, and prevent the date from ever changing, even if you modify the field to 'done' again. Change the last 'if' statement to the following:

If (UCASE(cell.Value) = "DONE" And Target.Worksheet.Cells(cell.row, 1).Value = "") Then