Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: Simple Excel Date question

    I want to create a spreadsheet that automatically enters the date that I started a task when I enter text into a field...that's simple enough with:

    =IF(A2<>"",NOW(), "")

    But that will change the date everytime I open the spreadsheet. I'm trying to set it up so the current date is placed in a cell and then stays that way.

    So for example, I enter text in a cell, the date 11/10/2006 is entered in another cell and the date is fixed to 11/10/2006. I don't want it to change to 11/11/2006 when I open it tomorrow. Obviously the above formula will change the cell to whatever the current date is each time I open the spreadsheet.
    Thanks,
    Bill

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Chip Pearson has some excellent help for this kind of work:

    Chip Pearson's site
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2006
    Posts
    1
    How about applying a macro under worksheets:


    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'adjust e2 to where your formula is.
    Range("E2").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    End Sub
    Last edited by andre5; 11-16-06 at 02:24.

  4. #4
    Join Date
    Feb 2006
    Posts
    113

    Arrow

    Hi Andre,

    Or
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'adjust e2 to where your formula is.
    Range("E2").Value = range("E2").Value
    End Sub
    beeyule, if you can describe more particulars of the situation, maybe a simple bit of code could achieve what you want. Such as this code (when in the code module for a worksheet) will enter the date in column A every time a change is made to a cell in a different column. Something similar can be done maybe to suit your requirements?

    regards,
    Fazza

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cel As Range
    
    Application.EnableEvents = False
    
    For Each cel In Target
        If cel.Column <> 1 Then Cells(cel.Row, 1).Value = Date
    Next cel
    
    Set cel = Nothing
    
    Application.EnableEvents = True
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •