Stop automatically refreshing date if a specific word is in another cell

[Fire Marshal]

Good evening. I have a cell (E7) automatically updating with today's date (=TODAY() ). I want to stop or lock E7 from continuing to update if D7 has the word "Approved" in it. Any help would be greatly appreciated. Thanks

 

[Bosquedeguate]

What date should if be a "Case" is approved? We need to pull the date from somewhere

Column will contain either Today's date or some other date. The column will probably have a formula like:

=IF(D7="Approved",?,TODAY()) ... the "?" here is what date should be used if the Case is approved. If you add a column labeled "DATE APPROVED" then the "STATUS DATE" column could be either the approved date or blank. So the formula for E7, for example would be

Either
=IF(D7="Approved",H7,TODAY()) - the date from the new column
or
=IF(D7="Approved","",TODAY()) - blank

Also, please note that the DAYS column appears to be calculated from the STATUS DATE - DATE REVIEWED
so you will need either a date in the STATUS DATE column (i.e. it cannot be blank) or the formula in the DAYS column will have to change.

Therefore, I think the easiest solution for you is add a APPROVED DATE column to your sheet and
Change the formula in Column E7:En =IF(D7="Approved",H7,TODAY()) - if H7 is the new column for the APPROVED DATE

 

[bebo021999]

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

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

 

[Bosquedeguate]

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

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

Note: They have 12 worksheets (1 for each month) - if VBA code is used it would have to be applied to each sheet. If the Change event route is the one they like. You code should be in a separate module and the Change event on each applicable tab should call your Sub. Or apply your code at the workbook level.

 

[Fire Marshal]

What date should if be a "Case" is approved? We need to pull the date from somewhere

Column will contain either Today's date or some other date. The column will probably have a formula like:

=IF(D7="Approved",?,TODAY()) ... the "?" here is what date should be used if the Case is approved. If you add a column labeled "DATE APPROVED" then the "STATUS DATE" column could be either the approved date or blank. So the formula for E7, for example would be

Either
=IF(D7="Approved",H7,TODAY()) - the date from the new column
or
=IF(D7="Approved","",TODAY()) - blank

Also, please note that the DAYS column appears to be calculated from the STATUS DATE - DATE REVIEWED
so you will need either a date in the STATUS DATE column (i.e. it cannot be blank) or the formula in the DAYS column will have to change.

Therefore, I think the easiest solution for you is add a APPROVED DATE column to your sheet and
Change the formula in Column E7:En =IF(D7="Approved",H7,TODAY()) - if H7 is the new column for the APPROVED DATE

There are four selections in the Status column, "Submitted," "Approved," "Waiting for correction," and "On Hold." When the status is changed in column D, it needs to add the date the status changed and not update again unless the status changes.

 

[Bosquedeguate]

I think @bebo021999 ’s Worksheet_Change event VBA code is your best option. (See previous post).

 

[Fire Marshal]

I think @bebo021999 ’s Worksheet_Change event VBA code is your best option. (See previous post).

That worked for me. Thanks a bunch!