Forum Discussion
static date update
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().
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 SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
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.