Results 1 to 6 of 6

Thread: Now() (2000)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Now() (2000)

    I am using the following IF statement for a date stamp. =IF(B4="","",NOW())
    When someone makes an entry, I want column A (that has the if statement) to automatically put the date of the entry. My question is - does the date stay the same or does it change to the current date when I bring the spreadsheet up the next day. My next question is - is there a date stamp function or macro that I can use for a datastamp?

    Thanks
    Linda

  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: Now() (2000)

    The date will be changed every day (actually the contents will change every second!
    You can have them use ctrl-; to add the date manually

    This macro added to the sheet object of the sheet of interest will add the date and TIME (use DATE instead of NOW in the function if you only want the date) to column A of the rows whenever a value in B is changed. Change as appropriate

    Steve

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    If Not Intersect(Target, Columns("B")) Is Nothing Then
    For Each rCell In Intersect(Target, Columns("b"))
    rCell.Offset(0, -1).Value = Now ' or use Date for just the date
    Next
    End If
    End Sub</pre>


  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now() (2000)

    Thanks - that works fine. Another request - Is there a macro that can be used to date stamp each worksheet in a workbook automatically when anything on that worksheet is changed. Location of datestamp would be in the lower right corner of spreadsheet about 30 rows down.

    Thanks so much for your help......

  4. #4
    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: Now() (2000)

    This macro will place the date and time in cell N30 of the worksheet named sheet 1 (change as appropriate) whenever a cell is changed in excel (does NOT matter what sheet the changed cell is on). Code goes in thisworkbook object in VB

    Steve

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Worksheets("sheet1").Range("N30").Value = Now
    End Sub</pre>


  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now() (2000)

    I am being a pest but is there anyway to time stamp each spreadsheet separately in a workbook? Is there code that can go behind sheet1, sheet2 etc.

    Thanks for your help and patience

  6. #6
    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: Now() (2000)

    This will add the time stamp to Cell N30 of the sheet whose cell has changed.

    Steve

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Sh.Range("N30").Value = Now
    End Sub</pre>


Posting Permissions

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