如何在Google Sheets中创建基于指定单元格输入的静态日期戳功能（不覆盖已有数据）

Hey Carole, let’s get this static date issue sorted out for good! I know you’ve tried formulas and scripts before but hit snags like dates auto-refreshing or scripts not firing—this solution fixes both, plus it’ll leave your existing manual dates in column I totally untouched.

This uses a simple Apps Script that triggers automatically when you edit the sheet, writing a static date value instead of relying on volatile formulas that update every time the sheet opens.

Step 1: Open the Script Editor #

• Head to your sheet, click Extensions > Apps Script to open the editor window.
• Delete any default code that’s already there.

Step 2: Paste the Custom Script #

Drop this code into the editor—it’s tailored to your exact needs:

function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  const editedCell = e.range;
  
  // Only act if we edited column H (column index 8) and entered "yes" (case doesn't matter)
  if (editedCell.getColumn() === 8 && editedCell.getValue().toLowerCase() === "yes") {
    const dateCell = activeSheet.getRange(editedCell.getRow(), 9); // Column I is index 9
    
    // Skip if column I already has a date (no overwrites!)
    if (dateCell.getValue() === "") {
      // Format the current date as MM-dd-YYYY and write it as a static value
      const staticDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd-yyyy");
      dateCell.setValue(staticDate);
    }
  }
}

Step 3: Save and Test #

• Click the save icon (💾) and name your project something like "StaticDateForYes".
• Go back to your sheet, type "yes" in any empty column H cell where column I is blank. The current date should pop up in column I right away.
• Close and reopen the sheet to confirm: the date stays put—it’s a static text value now, no auto-refreshing!

If you’re working in Excel instead, here’s a VBA script that does the same job:

Step 1: Open the VBA Editor #

• Right-click the sheet tab at the bottom of your Excel window, then select View Code.

Step 2: Paste the VBA Code #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if we edited column H and entered "yes" (case-insensitive)
    If Target.Column = 8 And UCase(Target.Value) = "YES" Then
        Dim dateCell As Range
        Set dateCell = Target.Offset(0, 1) ' Column I is one column to the right
        
        ' Don't overwrite existing dates
        If dateCell.Value = "" Then
            dateCell.Value = Format(Date, "MM-dd-yyyy")
            dateCell.NumberFormat = "MM-dd-yyyy"
        End If
    End If
End Sub

Step 3: Save and Test #

• Save your workbook as a .xlsm (macro-enabled workbook) to keep the script active.
• Type "yes" in column H (where column I is empty) and the static date will appear. When you reopen the file, the date won’t change, and your existing manual entries stay safe.

Quick Troubleshooting #

• Google Sheets: Double-check the trigger is enabled by going to Edit > Current project's triggers—the onEdit trigger should be listed (it’s created automatically when you save the script).
• Excel: Make sure you enable macros when opening the workbook—Excel disables them by default for security, so you’ll need to allow them for the script to run.

内容的提问来源于stack exchange，提问作者Carole Spake