Another Excel Question

Discussion in 'Computers' started by drobbins, Oct 12, 2007.

  1. drobbins

    drobbins Screenwriter

    Joined:
    Dec 2, 2004
    Messages:
    1,870
    Likes Received:
    1
    Real Name:
    Dave
    Is there a formula in excel that I can use to automatically add the current date, but once it is there, it will not change each day? Example: If I enter data in A1, then B1 will have today’s date entered automatically. Then tomorrow I enter info in A2 and B2 has tomorrows date entered automatically. The date in B1 did not change from today’s date.
    Thanks,
     
  2. Tekara

    Tekara Supporting Actor

    Joined:
    Jan 8, 2003
    Messages:
    783
    Likes Received:
    0
    Sure, you'll need to use some visual basic though. This page will help with events and it covers using worksheet|change which is what you'll need to check for A1 changing, then you'll just have B1 change it's contents to today(). Make sure to lock down B1 so it can't be manually edited by mistake later!

    http://www.cpearson.com/excel/Events.aspx

    Hope that helps
     
  3. drobbins

    drobbins Screenwriter

    Joined:
    Dec 2, 2004
    Messages:
    1,870
    Likes Received:
    1
    Real Name:
    Dave
    Thanks for the reply. It looks a little more complicated than I expected, but I will give it a try.
     
  4. SethH

    SethH Cinematographer

    Joined:
    Dec 17, 2003
    Messages:
    2,867
    Likes Received:
    0
    I would take a slightly different approach than the one Robert suggests.

    It sounds like you will be updating this daily. For instance, today you will put data in A1, tomorrow in A2, etc. That would require some excessively complicated VBA, IMO (unless I'm overlooking something).

    I would instead create a button and call it "Date Stamp" or something like that and require the user to click this button after entering that day's data in column A. Put something like this in the button_click event:

    Sub Button_Click()
    Dim rngDateCell as Range

    Set rngDateCell = Range("B65536").End(xlup).offset(1,0)
    rngDateCell = format(now(),"Long Date")

    End Sub

    You should also have some error handling and such in there, but I think that will do what you want assuming you will always use consecutive rows to input the data.
     
  5. drobbins

    drobbins Screenwriter

    Joined:
    Dec 2, 2004
    Messages:
    1,870
    Likes Received:
    1
    Real Name:
    Dave
    Seth,
    Yes, Data is going to be entered daily on this sheet. This Button, is it located on the tool bar or in the spreadsheet? This spreadsheet is located on a company shared drive and is accessed by multiple users.
     
  6. Tekara

    Tekara Supporting Actor

    Joined:
    Jan 8, 2003
    Messages:
    783
    Likes Received:
    0
    Nah, the the event I suggested can be applied to a range of cells. In this case, your range would just be the singular cell. And it'll update the date cell whenever it's altered, so there's no chance for a mistake in forgetting to press a button.

    Although, just like the button you'll need to work up a copy of the code for each cell.
     

Share This Page