Share via

Add a Fixed Date Stamp to excel document

Anonymous
2022-05-18T17:20:02+00:00

I am creating a template document in excel that a user will be able to enter their data in the unprotected fields and save to a new file. I would like to include a date stamp that would be assigned to the document when the user opens the template to assign the current date & time, but retain that date and time for their saved document. In other words, I do not want the date to update every time they reopen the document they have saved with their data.

Is there a way that I can have the template document show a current date / time, but when the document is saved that date / time becomes static?

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-05-19T14:02:50+00:00

    Re: more

    '---

    Sub AddStartDate()
    If VBA.Len(ThisWorkbook.Worksheets(1).Range("G2").Value) > 2 Then
    Else
    ThisWorkbook.Worksheets(1).Unprotect Password:="Sludge"
    ThisWorkbook.Worksheets(1).Range("G2").Value = "'" & _
    VBA.Format(VBA.Date & " " & Time, "MMMM dd yyyy hh:mm am/pm")
    ThisWorkbook.Worksheets(1).Protect Password:="Sludge"
    End If
    End Sub
    '---

    Replace Sludge with your password.

    '---
    Nothing Left to Lose

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2022-05-19T13:21:03+00:00

    Thank you - I am able to open the protected document and not get the error message, however, I would like the date to still populate. I just don't want that field open where the user can edit it.

    0 comments
  3. Anonymous
    2022-05-19T12:54:03+00:00

    Re: "I got an error"

    Add this code line, as the first line in the AddStartDate sub...

    If ThisWorkbook.Worksheets(1).ProtectContents Then Exit Sub

    '---
    Nothing Left to Lose

    0 comments
  4. Anonymous
    2022-05-19T11:57:02+00:00

    Thank you!!! It works wonderfully.

    One issue I had - I want to protect the "locked cells" so the user only can edit the input cells. I do not want them to be able to alter the date. When I reopened the document after protecting it, I got an error and it highlighted this code

    0 comments
  5. Anonymous
    2022-05-19T02:04:15+00:00

    Re: "Is there a way that I can have the template document show a current date / time, but when the document is saved that date / time becomes static?

    Place this code in the "ThisWorkbook" module...

    '---

    Private Sub Workbook_Open()
    Application.OnTime VBA.Now, ThisWorkbook.Name & "!AddStartDate"
    End Sub
    '---

    Place this code in a standard module...

    '---

    Sub AddStartDate()
    If VBA.Len(ThisWorkbook.Worksheets(1).Range("B1").Value) > 2 Then Exit Sub
    ThisWorkbook.Worksheets(1).Range("B1").Value = "Born on: " & _
    VBA.Format(VBA.Date & " " & Time, "MMMM dd yyyy hh:mm am/pm")
    End Sub

    '---

    Adjust specified worksheet and cell location to suit.

    '---
    Nothing Left to Lose

    https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    (free excel programs)

    0 comments