Auto date insertion

[bryan1009]

I need to auto insert date in A1 when a2 is populated

[#2]

@bryan1009 

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 Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook.

[#3]

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

@HansVogelaar 

[#4]

@bryan1009 

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?

[#5]

My apologies. I hadn't enabled Iterative calculation. Took me a bit of |Googling to find it. I thought it was on the toolbar ribbon. Doh. No, it opened on the 7th as you forecast.
Again, Thank you