Auto date change Closed

Question

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!!!!!!!!!!

rizvisa1 · Answer

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)

rizvisa1 · Answer

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

Auto date change

2 responses

Similar discussions

Subscribe To Our Newsletter!