Previous Topic Next Topic

Auto date change

Closed
StormDronk - May 17, 2010 at 04:00 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 17, 2010 at 08:23 AM
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!!!!!!!!!!
Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 17, 2010 at 05:52 AM
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)
Stormdronk
May 17, 2010 at 06:30 AM
Hi, thanks. New with this, how do I use a macro to solf this problem.

Thanks again for the help!!!
Answer 2 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 17, 2010 at 07:05 AM
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
StormDronk
May 17, 2010 at 07:58 AM
cant save, error message, must save as macro type fil. Dont know where to select this type file to save.

Thanks!!!
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 17, 2010 at 08:23 AM
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