Nested IF function to stop TODAY() updating to TODAY's date

Hi all,

Question related to the TODAY() formula. I have a very basic table with 2 columns: TASK and DATE

The idea is: when you enter a task in the TASK column, it automatically enters the date on which the task was added to the list.
I manage this with the formula:

=IF(NOT(ISBLANK(A2));TODAY();"")

Capture.PNG


Problem: when you open the Excel, it recalculates everything and automatically changes every old date into TODAY()'s date. It basically runs the formula again.

I thought about solving it with a nested IF function, but I don't get my head around it, and it feels like I am building a loop (I know the parentheses are not ok, just trying to explain the formula.

=IF(NOT(ISBLANK(A2));IF(ISBLANK(B2);TODAY(););)

In words:
If A2 is not blank
>>>>THEN
>>>>IF B2 is blank
>>>>>>>THEN add today's date,
>>>>>>>otherwise (IF B2 is not blank) don't do anything
otherwise (if A2 is blank)
THEN leave B2 empty


A would need a solution without VBA if possible ...

Thanks in advance!

You would need to use VBA for this task, likely the Worksheet_Change event to see if a cell in column A has changed, and if so update the cell in column B of that row to reflect the date A2 was entered. (You may also want to have code to update the date if A2 was already filled and has now changed (if the same, don't update date). And if deleted, remove the date.)

Using a combination of the Worksheet_Change, Worksheet_SelectionChange and a global variable (in a module), this may work for you.
The following code goes into the worksheet code module for the sheet with your tasks/dates (right-click tab, select View Code, paste this code into the VB editor)

Private Sub Worksheet_Change(ByVal Target As Range)

' Don't run macro if multiple cells changed at once
If Target.Cells.Count > 1 Then Exit Sub

' Check if the Target cell (acted upon cell) intersects with Column A.
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    
    ' If target cell is blank, delete date from column B.
    If Target.Value = "" Then
        Target.Offset(0, 1).Value = ""
    
    ' Else, if target cell value is different from lastCell variable
    ' that was updated when you selected the cell, then update the
    ' date in column B.
    ElseIf Target.Value <> lastCell Then
        Target.Offset(0, 1).Value = Date
        
    End If

End If
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lastCell = Target.Value
End Sub

This line of code sets a global variable, and goes in a module. In the VB Editor, click Insert > Module, then paste this line. It basically captures the cell value of any cell you click on while on the sheet with the 'SelectionChange' event code. This is so it can compare the previous value with the new value. (If the cell was "Test Task 1", and you re-type "Test Task 1", you probably don't want the date to change, hence this check. If it was "Test Task 1" and now it's "Test Task 2", a new date will appear in column B.)

Global lastCell As Variant