static date update

harshulz 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const MyRange = "C2:C100" ' Change as needed
    Dim rng As Range
    Dim cel As Range
    Set rng = Intersect(Range(MyRange), Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In rng
            If cel.Value = "" Then
                cel.Offset(0, -1).ClearContents
            Else
                cel.Offset(0, -1).Value = Now
            End If
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.