/ How to

How To Display and Update the Current Date Automatically in Excel Cells

Displaying the current date in an Excel spreadsheet can be very useful for tracking changes over time. Fortunately, Excel provides several straightforward methods to insert and automatically update the date in cells. This comprehensive guide will walk through the various options to display the date, from keyboard shortcuts to Excel formulas.

Insert Today’s Date as a Static Value

The quickest way to add the current date to a cell is by using a keyboard shortcut. This enters the date as a static, unchanging value:

  • Ctrl + ; – Inserts the current date
  • Ctrl + Shift + ; – Inserts the current time
  • Ctrl + ;, then Space, then Ctrl + Shift + ; – Inserts the current date and time

The date does not update automatically when using this method. To update, you would need to delete the existing value and re-enter it.

Use the TODAY() Formula to Dynamically Update the Date

To have the date update automatically, use the TODAY() formula instead of a shortcut:

=TODAY()

This returns the current date, which updates each time the spreadsheet calculates. Some key points:

  • The date updates automatically whenever the spreadsheet calculates – when opened, edited, etc.
  • The time portion is not included, only the date.
  • Date formatting can be applied to control how the date displays.

Here is an example usage:

=TODAY()

Displays as: January 24, 2024

And a formatted variation:

=TEXT(TODAY(), "dddd, mmmm dd, yyyy")

Displays as: Wednesday, January 24, 2024

Use the NOW() Formula for Date and Time

To return both the current date and time, use the NOW() function instead of TODAY():

=NOW()

Key points:

  • Includes hours, minutes, and seconds
  • Updates automatically like TODAY()
  • Can also be formatted using TEXT() or custom number formats

Example:

=TEXT(NOW(), "mm/dd/yyyy hh:mm AM/PM")

Displays as: 01/24/2024 09:15 AM

VBA Macros to Insert Date and Time

In VBA, the Date and Now functions can insert values into cells:

Sub AddDate()

    Range("A1").Value = Date
    Range("B1").Value = Now

End Sub

The Date function returns only the date, while Now includes both date and time.

The date format can also be customized with the Format function:

Range("A1").Value = Format(Date, "dddd, mmmm dd, yyyy")

Updating Date Values Automatically

Formulas that return the current date, like TODAY() and NOW(), update automatically in a few cases:

When the spreadsheet calculates:

  • Opening the file
  • Editing a cell
  • Certain functions like data sorts

Manual calculation updates:

  • Pressing F9 to calculate
  • Clicking Calculate Now under the Formulas tab

Disable automatic calculations (not recommended):

  • File > Options > Formulas > Workbook Calculation > Manual

Overall, allowing automatic calculations is best to keep dates updated.

Date and Time Format Options

Whichever method you use to insert dates, the format can be customized:

Format cells:

  • Right-click cells > Format Cells > Date category
  • Use built-in formats like “March 3, 2024”

Text function:

  • TEXT(A1,"dddd, mmmm dd, yyyy")
  • Customize the display format

Custom number formatting:

  • Create custom display formats like “dd/mm/yyyy”
  • Useful for times also: “hh:mm AM/PM”

Proper date formatting ensures dates are clear and human-readable at a glance.

In Conclusion

Displaying current dates is straightforward in Excel with keyboard shortcuts, formulas like TODAY and NOW, and VBA macros. Automatic or manual recalculation keeps the dates updated. Date and time formatting then controls how the values appear.

Incorporating dates into Excel reports, dashboards, and data models enables better tracking and timeliness. Whether you need a simple timestamp or code to update many cells, these methods provide the flexibility to add and format dates however needed.

Related Posts