Forum Discussion
Auto date insertion
I need to auto insert date in A1 when a2 is populated
Option 1: using a formula.
Before creating the formula, select File > Options > Formulas.
Tick the check box 'Enable iterative calculation', then click OK.
Enter the following formula in A1:
=IF(A2="", "", IF(A1="", TODAY(), A1))
Format A1 as a date.
Option 2: using VBA.
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) If Not Intersect(Range("A2"), Target) Is Nothing Then If Range("A2").Value <> "" And Range("A1").Value = "" Then Application.EnableEvents = False Range("A1").Value = Date Application.EnableEvents = True End If End If End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook.
Thank you. #1 works for me, not being VBA competent, but the date changes to the current date wheneverIopenthespreadsheet. Which isnt what i wanted, sadly. I need the date to be static
tThanks
Bryan Evans
That's weird. If you followed the instructions exactly as posted, the date will be static.
I have attached a sample workbook. When I saved it, A1 contained the 7th of March, 2024.
Does it change to the current date when you open the workbook?
