Date & Time Formula

[blandreth]

I'm looking for a simple excel formula that will populate a cell with the date and time when the file is opened.

I appreciate the feedback.

Brian

 

[njimack]

=now()

 

[Joe4]

There is a formula that will return the current date/time. It is:
=Now()

However, note that is not static, so it will continue to change.
If you want to capture the date/time that it was opened, and "freeze it" in time, you would need to use VBA.
There are literally thousands of threads on this, called "Date Stamp" or "DateTime Stamp", though most of them will be using the "Worksheet_Change" event procedure, which runs when a cell is changed, whereas you will probably need to use a "Workbook_Open" event procedure, which runs when a workbook is opened.

If you are open to VBA and need help doing that, please provide the sheet name and cell that you want this value to go into.

 

[Gerald Higgins]

=NOW() gives you the date and time now, which will be the same as when the file was opened, IF calculation was set to automatic when you opened the file, and you don't subsequently re-calculate.
If you DO have a later re-calculation, =NOW() will show a different result.

What I would do is have an auto-open macro that uses NOW() and then converts the formula to a value, whenever the file is opened.

I'm not a code expert, there may be better ways of doing this.

 

[blandreth]

Thanks for the feedback. That should work for what I'm doing.

 

[blandreth]

I was a little quick to say the =now() would work. For the report I'm working on, I have macro's that save the report with a different name after it is populated and closes the blank report without saving to keep it blank. I will need the saved report to keep the date and time after it is saved. I'm downloading the report data to a log file (also with a macro), so I will need the saved report data to match the log data.

The sheet name is "report" and the cell I want this information is G13. Can the code be added to the "save" macro code? So when I run the save macro, it will save the file with a new name and lock the date and time in G13.

I appreciate your help!

 

[Joe4]

The sheet name is "report" and the cell I want this information is G13. Can the code be added to the "save" macro code? So when I run the save macro, it will save the file with a new name and lock the date and time in G13.

Just add this line to your "Save" macro:

Sheets("report").Range("G13").Value = Now()

 

[blandreth]

Would I just add this as the last line before the End Sub?

 

[blandreth]

Here is my save code:

Sub SaveFile()
'
' SAVEFILE Macro
'Dim sNameSheet As String

sNameSheet = Range("NAME").Value

ActiveWorkbook.SaveAs Filename:="C:\Users\Brian.landreth\Documents\Weld Reports\2019 Weld Reports" & sNameSheet


'
End Sub

 

[Joe4]

You will want to it before the Save line, or the file will be saved without it!