Today() or No Change if cell is already a date

tomc72 

There are two ways to do this:

1. Using iterative calculation.
2. Using VBA code.

For iterative calculation:

• Select File > Options.
• Select Formulas.
• Tick the check box 'Enable iterative calculation'. You have to do this only once.
• In G2, enter the formula =IF(AND(K2:L2<>""),IF(G2<>"",G2,TODAY()),"")
• Fill down.

For a VBA solution:

• Right-click the sheet tab.
• Select 'View Code' from the context menu.
• Copy the code listed below into the worksheet module.
• Switch back to Excel.
• Save the workbook as a macro-enabled workbook (.xlsm).
• Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    If Not Intersect(Range("K2:L" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("K2:L" & Rows.Count), Target)
            r = rng.Row
            If Range("K" & r).Value <> "" And Range("L" & rng.Row).Value <> "" Then
                Range("G" & rng.Row).Value = Date
            Else
                Range("G" & rng.Row).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub