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!