Forum Discussion
Auto date insertion
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?
- 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
