It requires VBA code, in worksheet_Change events.
Below code is placed in worksheet module (not general module), trigger change made in range D7 to last cell in D.
Initial formula in D is =TODAY(), if D change to "approve", E will be fixed to that date.
Right click on tab's name, View Code, then paste the code into
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, cell As Range
lr = Cells(Rows.Count, "D").End(xlUp).Row
If Intersect(Target, Range("D7:D" & lr)) Is Nothing Then Exit Sub
For Each cell In Target
With cell.Offset(0, 1)
Select Case cell.Value Like "Approve*"
Case True
.Value = .Value
Case Else
.Formula = "=today()"
End Select
End With
Next
End Sub
View attachment 96413