Date & Time Formula

blandreth

Board Regular
Joined
Jan 18, 2018
Messages
56
Office Version
  1. 365
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
=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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
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:
Code:
Sheets("report").Range("G13").Value = Now()
 
Upvote 0
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
 
Upvote 0
You will want to it before the Save line, or the file will be saved without it!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top