If Funcion with today's date

[fapb48]

Hi,
I am trying to do a simple if function and include today's date in the formula.

My current formula is for example : =IF(D2="Inactive", TODAY(),"")

The problem with the above formula is that if i open the file tomorrow it will show tomorrow's date..

is there a way to just get the IF formula to automatically display the correct date instead of typing today's date in the formula?

Thanks

 

[Joe4]

Are you trying to "capture" the date when cell D2 changes to "Inactive"?
So that when the value is set, it stays at that data and never changes?

If so, I think you are going to need VBA to do that. We may be able to have it work automatically, depending on a few factors.
How is the value in D2 being updated? Is it manually being updated, or is there some sort of formula in cell D2?
And do you need this ONLY for cell D2, or for ALL the cells in column D?

 

[fapb48]

Are you trying to "capture" the date when cell D2 changes to "Inactive"?
So that when the value is set, it stays at that data and never changes?

If so, I think you are going to need VBA to do that. We may be able to have it work automatically, depending on a few factors.
How is the value in D2 being updated? Is it manually being updated, or is there some sort of formula in cell D2?
And do you need this ONLY for cell D2, or for ALL the cells in column D?

Thats correct. If the Collumn D has 2 values.. Inactive and Active.. If i change it to inactive the IF needs to capture what date it was changed to Inactive and that date should not change.. I can type "18/05/2023" instead of TODAY() but that is a pain to do that to every cell.

 

[Joe4]

OK, here is VBA code which will automatically hard-code the current date in column E if column D is set to "Inactive" (you did not say which column you wabnt the date in, so I assumed column E).

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Update if column D updated after row 1 and value is "Inactive"
    If (Target.Column = 4) And (Target.Row > 1) And (Target.Value = "Inactive") Then
        Application.EnableEvents = False
'       Update column E with current date
        Target.Offset(0, 1).Value = Date
        Application.EnableEvents = True
    End If
    
End Sub

This should automatically do what you want as you manually update the values in column D.

 

[fapb48]

OK, here is VBA code which will automatically hard-code the current date in column E if column D is set to "Inactive" (you did not say which column you wabnt the date in, so I assumed column E).

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
   
'   Update if column D updated after row 1 and value is "Inactive"
    If (Target.Column = 4) And (Target.Row > 1) And (Target.Value = "Inactive") Then
        Application.EnableEvents = False
'       Update column E with current date
        Target.Offset(0, 1).Value = Date
        Application.EnableEvents = True
    End If
   
End Sub

This should automatically do what you want as you manually update the values in column D.

thank you very much. Worked perfectly!

 

[Joe4]

You are welcome.
Glad I was able to help!