Is there a way to have Excel input the date as in =Now() but not update every time you open the file?

Question

I want to have it enter the date that the file was first saved or created for this template, but when I open it up again I don't want that to change.

Answer 1

now() is a volatile formula. It updates everytime the worksheet is recalculated.

I would just manually enter the date and time and leave it as that. Use control + semi-colon. It can't get easier than that.

But if you are vba inclined, you can try this. But I don't think it worth the effort.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Range("a1") = 0 Then
Range("a1") = Now()
End If
End Sub

Answer 2

The only way I know to accomplish what you want is use a simple macro. If you haven't worked with macros, this may seem complicated, but print this out, and follow the instructions, and it won't take long.

First name the cell in your template where you want the date created to be entered. Let's name it DateCreated (you can do this by selecting the cell, clicking on the box to the left of the formula bar that shows the cell number, and typing DateCreated and hitting Enter)

Name another cell SaveNoDate. You are going to enter Yes in this cell whenever you want to save the template with the DateCreated field blank. When the template (or file saved from the template) is opened, the SaveNoDate cell is automatically set to blank by the code below. You can hide the row or column this cell is in so other users don't see it.

Now this is the tricky part. You need to copy and paste the code below exactly as follows: Press Alt-F11 to bring up the Visual Basic editor. On the left there should be a window labeled Project - VBAProject (if not select Project Explorer under the View menu). Find the project with the name of your spreadsheet in the window. Double-click on the icon labeled "ThisWorkbook" It will bring up a code window. Copy and paste the code below, select File - Close and Return to Microsoft Excel.

Now in the cell you named SaveNoDate, enter Yes, then save the template.

The next time you open the template and save it or do a save as, the time and date will be automatically inserted in the cell you named DateCreated. Thereafter, this time and date will not change.

If you want to modify your template and save it without a date being inserted, delete the contents of the DateCreated cell and enter Yes in the SaveNoDate cell, then save it.

I've tested it and it works!



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Range("DateCreated").Value = "" And _
Range("SaveNoDate").Value = "" Then
Range("DateCreated").Value = Now
End If
End Sub

Private Sub Workbook_Open()
Range("SaveNoDate").Value = ""
End Sub

Answer 3

NOW is a function that returns the CURRENT DATE AND TIME from the system clock. There's no way to use that function and NOT have it update.