Mar 20 2022 09:33 AM
hi everyone,
i have been making data for my company, therefore i need help as follows
i am using =if(c2<>"",today(),"") in cell b2, however i m failing because date is progressively update as day changes.
thank you,
harshul
Mar 20 2022 09:42 AM
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.
Mar 22 2022 05:39 AM
Mar 22 2022 06:05 AM
SolutionSelect 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().
Mar 22 2022 01:25 PM
Mar 22 2022 01:28 PM
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.
Mar 22 2022 06:05 AM
SolutionSelect 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().