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 Current Date and Time
To show the current date and time in a cell, use the NOW function:
- =NOW()
This formula result will update automatically, when the workbook recalculates.
Manually Enter 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 date and time as value, in the same cells:
- Enter the date (Ctrl+;)
- Then type a space character
- Then enter the time (Ctrl+Shift+;)
Determine When 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. There is sample code in the next section.
- Note: You could also create a User Defined Function (UDF) to calculate the date. However, I find that UDFs can slow down a workbook, so avoid them, if possible.
Macro VBA Code – Last Saved Date
Here is the code that inserts the workbook’s last saved date. It’s entered on a sheet named Data Entry, in cell A1.
Tip: You could add an event procedure in your workbook, so this code runs automatically, any time the workbook is 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.
First, thanks to for blog and site.
my probleme is: i have a 15 mn step in colomn (ex: 1st row = “10/11/2017 07:00:00” 2d row= “10/11/2017 07:15:00” and so on
i have a probleme when i compare dates and times manually entered in excel sheet and dates and time when i use the increment button in bottom right cell.
the difference is equal (1.157446E-08) for exemple. and my macro VBA dont work
why excel do that ? thank for response.