Excel - Change date automatically

June 2017




Issue


I am using this formula on my work sheet:
  • =IF(OR(A1=1, A1=2, A1=3), TODAY(),"") 

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 

Solution


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

Note that


Thanks to rizvisa1 for this tip on the forum.

Related


Published by aakai1056. Latest update on November 14, 2011 at 06:22 AM by aakai1056.
This document, titled "Excel - Change date automatically," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).