Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Automatic Date?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to find a way of automatically inserting a date when a second cell has been altered. For example A1 = blank, if I then put any entry in A2, I want to show the date in A1 that A2 was altered.

    I have already used the IF function using the NOW() function as an argument - but of course the date always changes thereafter whenever the workbook is opened or recalculated, as the cell is constantly reflecting (correctly) the NOW() function.

    I just want to know when a cell was changed - sounds too simple to be difficult but I can't find a way of doing it. Can anyone help me with this?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Create a macro that goes to your date cell A1 and enters =today() whenever a user types anything into A2.

    Make sure to add another step in your macro to CopyPasteValues in the date cell so that the today formula is not there to update.

    Finally, create a button called "datestamp" and place it next to A2; assign your datestamp macro to it - users should hit it after updating.

    There may be better ways with better VBS coding, but this is a simple way that unfortunately depends on your user actually hitting the update button after entering data.


    [ This Message was edited by: Duane on 2002-03-21 13:38 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    will ctrl+: not do the job>>>>>
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this and see if it will work for you....

    =if(isblank(a2),"",if(b2="",now(),b2))

    Adjust cells to fit.............


    Denny

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to you all for your efforts at solving this one - and I understand the approach that all of you have made, but I still have the problem.

    Perhaps I was beig over-simplistic in my original description.

    What I want to do is to have a list which automatically keeps a running total of a stock item ( I have already solved this part) but then I would like to have a column alongside the running total which tells me the date at which an adjustment to the stock level had been made. Is this any clearer?



  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could place a macro like this i the sheets own module. (rightclick on the sheettab, choose view code, and insert this code)
    Please adjust Target.column yourself (here 2 = Column B)
    Whenever a change is made to column B todays date is inserted in column C and it is not automatic updated.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then Target.Offset(0, 1) = Date
    End Sub

    regards Tommy

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Tommy, I'll certainly give that a try.

    Rgds,
    Richard

  8. #8
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, I like your clean, compact solution to the problem. Joe Was

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tommy,

    What can I say? Your response works just perfectly - by adding additional lines of code, I can now format any columns on the same worksheet to behave in exactly the way I required - pure brilliance as far as I'm concerned.

    I really do appreciate your help in solving this problem which, prior to this, I worried at for about three weeks!

    Once again many thanks for sharing your undoubted talent and experience.

    Richard

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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