static date update


hi everyone,
i have been making data for my company, therefore i need help as follows

  • i want date automatically update in cell b2 as someone input in cell c2,
  • though the date should be static. date should not progress with day change
  • harshulz_1-1647793511034.png

i am using =if(c2<>"",today(),"") in cell b2, however i m failing because date is progressively update as day changes.

thank you,

5 Replies


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

thanks hans, but can we formulate it without inserting macro or vba?
best response confirmed by harshulz (Contributor)


Select File > Options > Formulas.

Tick the check box "Enable iterative calculation" and set "Maximum iterations" to 1, then click OK.

Select the range where you want the date, e.g. B2:B100.

Enter the formula =IF(C2<>"",IF(B2<>"",B2,NOW()),"") and confirm with Ctrl+Enter to populate the entire selection.

This will enter the date and time; if you only want the date, use TODAY() instead of NOW().

yep this formula works, just waiting for day to end.
but i wonder u made this iteration to 1. what does actually this meant ?


If you enable iterative calculations, you allow circular references: formulas that refer to the cell containing the formula. This might cause repeated calculations, causing slow performance. By setting the maximum number of iterations to 1, you limit this.