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