static date update

[harshulz]

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
• 

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

thank you,
harshul

[#2]

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

[#3]

thanks hans, but can we formulate it without inserting macro or vba?

[#4 · Accepted solution]

@harshulz 

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

[#5]

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

[#6]

@harshulz

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.