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

 

Filed under: Excel, Programming, VBA