Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting Dates (Excel 2000 Sp3)

    I would like to have the date automatically inserted in a cell when a value is entered in the cell next to it. I have tried the date functions but they all update to the current date . Is there a way to automatically have the current date inserted so that it will not update when the date changes?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting Dates (Excel 2000 Sp3)

    The following will a value is changed in Column B (change as desired) will add the date to the cell to its left.
    The code goes in the worksheet object not a regular module
    Steve

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then _
    Target.Offset(0, -1).Value = Date
    End Sub</pre>


  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates (Excel 2000 Sp3)

    The code Steve gave you will put the date into the cell to the left every time the cell is changed. Is that what you want, or do you only want to put the date in if there is not already something there. If that is what you want, then change Steve's code to:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Offset(0, -1).Value = "" then
    Target.Offset(0, -1).Value = Date
    End If
    End If
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates (Excel 2000 Sp3)

    Thank-you for the code. I believe I need to elaborate further on what I am trying to accomplish. I'm trying to maintain a telephone call log where in one column the name of the caller is put ( Col. D ) and have the date automatically entered in the next column ( Col. E) when the caller's name is entered in the name column ( Col.D). The date functions I have tried to use to achieve this result all update and I need the date to remain static.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting Dates (Excel 2000 Sp3)

    Then how about this modification to my original:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("D")) Is Nothing Then _
    Target.Offset(0, 1).Value = Date
    End Sub</pre>


    or this change to Legare's code:
    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("D")) Is Nothing Then
    If Target.Offset(0, 1).Value = "" then
    Target.Offset(0, 1).Value = Date
    End If
    End If
    End Sub</pre>


    Steve

Posting Permissions

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