Auto date change

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

=IF(OR(A1=1, A1=2, A1=3), TODAY(),"")


Iam yusing this formula on my work sheet. The problem is that when I open my sheet the next day all the entry dates change to today, I need the previous day entrees to stay the same and only the new entry to show todays date, please can any body help!!


Status Days Flag Job Description Date
1 16 May 2010

2 17 May 2010
1 20 May 2010

3 21 May 2010


Thanks!!!!!!!!!!

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If you want to retain the date for good, then you have to use a macro or do it manually (copy, paste special as value)
Hi, thanks. New with this, how do I use a macro to solf this problem.

Thanks again for the help!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If value in A1 is going to be keyed in manually you can use events

to do that

1. Press ALT + F11 to start VBE
2. Press CTRL + R to show project explorer
3. Double click on the sheet where the value of A1 is going to be changed
4. Paste this code
Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' if the event was not triggered by first column (column A) then nothing to do
    If (Target.Column <> 1) Then Exit Sub
    
    ' if event was not triggered by row 1
    If (Target.Row <> 1) Then Exit Sub

    
    ' disable events so we dont get into cycle of events
    Application.EnableEvents = False
    
    If Target = 1 Or Target = 2 Or Target = 3 Then
        
        ' 1 row down, and one column to right
        Target.Offset(1, 1) = CDate(Now)
        
        ' same row and one column to right
        Target.Offset(0, 1) = ""
        
    Else
    
        ' 1 row down, and one column to right
        Target.Offset(1, 1) = ""
        
        ' same row and one column to right
        Target.Offset(0, 1) = CDate(Now)
            
    
    End If
    ' we are done now we can enable evetns again
    Application.EnableEvents = True
    
End Sub
cant save, error message, must save as macro type fil. Dont know where to select this type file to save.

Thanks!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
yeah since it is a macro, you have to save it as macro enabled file. and when you open the file make sure that you enabled the macro