Entering Dates and Times in Excel

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

 

You may also like...

4 Responses

  1. Andrew says:

    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

  2. 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.

  3. Ed says:

    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?

  4. 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.

Leave a Reply to Andrew Cancel reply

Your email address will not be published. Required fields are marked *