In yesterday's post I showed formulas you can use to pull information from a date in Excel. In some workbooks you'll enter dates manually, but often you'd like a date to be calculated automatically.
Calculate the Current Date
To show the current date in a cell, use the TODAY function:
=TODAY()
This date will update automatically when you open the workbook on a different date.
Calculate the Current Date and Time
To show the current date and time in a cell, use the NOW function:
=NOW()
This will update automatically.
Manually Enter the Current Date and Time
Instead of formulas, you can enter the date and time as values. These will NOT update automatically.
- To enter the current date in a cell as a value, press the Ctrl key and type a semi-colon (Ctrl+;)
- To enter the current time in a cell as a value, press the Ctrl key and type a colon (Ctrl+Shift+;)
- To enter both the date and time as value, enter the date (Ctrl+;), then a space character, then the time (Ctrl+Shift+;)
Determine When the Workbook Was Last Saved
In yesterday's post on dates, Mariusz asked how to find the date that the workbook was last saved. Unlike Word, excel doesn't have a menu command that will insert the last saved date. However, you can use a bit of programming to insert the date, assuming the workbook has been saved.
Sub GetLastSavedDate()
On Error Resume Next
Dim sSaveDate As String
sSaveDate = FileDateTime(ActiveWorkbook.FullName)
If sSaveDate = "" Then
MsgBox "Could not determine save date."
Else
Worksheets("DataEntry").Range("A1").Value _
= "Last Saved: " & sSaveDate
End If
End Sub
Wouldn't it be easier to save the last saved date doing something like this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("DataEntry").Range("A1").Value _
= "Last Saved: " & Format(Now, "mm/dd/yyyy h:mm AM/PM")
End Sub
Andrew, yes, that would be a quick way to stamp a file with the save date as you close the workbook.
Using the FileDateTime function, you could open any workbook, and figure out when it was last saved.
But let's say you want to captuer a date stamp in a comment within a cell whenever that cell was modified? How's that done?
Ed, there's sample code here for adding or editing an Excel Comment with Date Stamp
You can add the code to a workbook that opens automatically when Excel opens (e.g. Personal.xls), then add a toolbar button or shortcut key to run it.