Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date stamp (2000)

    While working on the spreadsheet I have the date and time the spread sheet was created. Therefater whenever I open the spreadsheet and amend the figures I would like a date &time stamp on same while keeping the last revision date. therefore whenever i open the spread sheet it will have the date and time stamp of the day as well as the last time the spread sheet was opened. Can the excelite gurus help me in this

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date stamp (2000)

    Where would you like to store these dates?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date stamp (2000)

    I would like the date and time to appear on the worksheet.

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

    You could add something like this the thisworkbook object:
    <pre>Private Sub Workbook_Open()
    With Worksheets("Sheet1")
    .Range("A2").Value = .Range("A1").Value
    .Range("A1").Value = Now
    .Range("A1").NumberFormat = _
    """Opened Now: "" mm/dd/yyyy hh:mm"
    .Range("A2").NumberFormat = _
    """Last Opened: "" mm/dd/yyyy hh:mm"
    End With
    End Sub</pre>


    It will read the last opened date/time from A1 and put it into A2, then put the current date/time in A1. it formats as time with the "verbiage" to explain the date.
    Change the worksheet, ranges, and formatting as desired

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date stamp (2000)

    As my name suggests I am absolute novice. Therefore pls tell me where to add this workbook object.
    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date stamp (2000)

    >> pls tell me where to add this workbook object

    With your workbook open, activate the Visual Basic Editor (Tools | Macro | Visual Basic Editor... or Alt+F11)
    Make sure that the Project Explorer is visible on the left hand side (View | Project Explorer or Ctrl+R.) It looks like the left hand pane of Windows Explorer, but with workbooks instead of drives and folders.
    If necessary, expand your workbook, then "Microsoft Excel Objects" within it until you see ThisWorkbook.
    Double click ThisWorkbook. This will open the code module associated with the workbook object.
    Select Workbook from the dropdown list in the upper left corner of the module window. This will create the outline of the Workbook_Open event procedure.
    You can now type Steve's code into it.

    Alternatively, you can copy his code to the clipboard, then paste it into Microsoft Word, copy it again and paste it into the code module. Unfortunately, you can't paste it directly into the code module, because of the way the code is formatted (it would come out as one line.)

    When you are done, you can switch back to Excel pressing Alt+F11 or by using any of the methods to switch between applications.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date stamp (2000)

    Thanks a TON mate. You made my day.
    Regards

Posting Permissions

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