Ask a question »

Excel - Change date automatically

August 2015


[Excel] Change date automatically




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.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-change-date-automatically.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel - Change date automatically » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.