Populate Date Daily

[kumara_faith]

Hi,

I have the following data entry table:

Book1
	B	C	D
6	Date	Profit	Loss
7	9/9/2021	25
Sheet1

Cell Formulas
Range	Formula
B7	B7	=IF(COUNTA(C7:D7)>0,TODAY(),"")

I am trying to build a formula in column B which will automatically populate the date for the particular day in which any profit or loss has been entered in column C or D. Example : today is 9/9/2021, the date should populate as 9/9/21. When we cross over to tomorrow, this date should not change and should remain as 9/9/21. Is there a way to modify my current formula in cell B7? Appreciate all the help.

 

[Joe4]

I don't think you can use a formula to do it, as TODAY() always returns the current date, so that value is constantly changing (and is not static).
However, you can use VBA to do it.

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

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated simultaneously
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if no value is updated
    If Target.Value = "" Then Exit Sub
    
'   Only run if columns C or D updated and after row 6
    If (Target.Row > 6) And ((Target.Column = 3) Or (Target.Column = 4)) Then
'       Put current date in column B
        Cells(Target.Row, "B") = Date
    End If

End Sub

This should automatically add the current date (as a hard-coded value) in column B as you make entries to columns C and D.

 

[kumara_faith]

Thank you Joe for the solution. Appreciate your time and patience.

 

[Joe4]

You are welcome.
Glad I was able to help!