Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows with at least n matching values
  • Merge tables with VLOOKUP in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRANSPOSE Function

Data Analysis Examples

  • How to calculate average last N values in a table in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Understanding Anova in Excel
  • How to create Gauge Chart in Excel
  • Conditional Formatting Icon Sets Examples in Excel

Data Validation Examples

  • Excel Data validation allow weekday only
  • Excel Data validation whole percentage only
  • Excel Data validation with conditional list
  • Excel Data validation must not contain
  • Excel Data validation exists in list

How to show last updated date stamp in Excel

by

To add a date stamp in a workbook to indicate a “date last updated”, you can use the TEXT function.

Note: 

The TEXT function can apply number formatting to numbers just like Excel’s built-in cell formats for dates, currency, fractions, and so on. However, unlike Excel’s cell formatting, the TEXT function works inside a formula and returns a result that is text.

You can use TEXT to format numbers that appear inside other text strings. In this case, we concatenate the test “Last update” with the result provided by the TEXT function, which picks up a date from column B and formats it using the supplied number format.

Worked Example:   Convert date to Julian format in Excel

You can embed the date in any format you like, using the codes that represent date formats (dd, mm ,yyyy, etc.)

 Formula

="Last update: "& TEXT(A1, "ddd, mmmm d, yyyy")

Worked Example:   Convert date to text in Excel

Explanation

In the example shown, the formula in C5 is:

="Last update: "& TEXT(B5, "ddd, mmmm d, yyyy")

With a named range

One convenient way to manage a “last updated” message in a large workbook is to use a named range to hold the date last updated, then refer to that named range in formulas elsewhere to display a last update message.

For example, you can name a cell something like “last_update”, and use that cell to enter the date last updated. Once you’ve defined the named range, you can use the formula below anywhere you like to display the same message:

="Updated: "& TEXT(last_update, "ddd, mmmm d, yyyy")

Whenever you change the date value in the named range, all formulas will update instantly, and all date stamps will stay in sync.

Worked Example:   Count dates in current month in Excel

Post navigation

Previous Post:

List holidays between two dates in Excel

Next Post:

How to join date and text together in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to split text with delimiter in Excel
  • Convert Numbers to Text in Excel
  • Remove text by matching in a cell in Excel
  • SUBSTITUTE function vs REPLACE function in Excel
  • How to Separate Text Strings in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Get age from birthday in Excel
  • Convert decimal minutes to Excel time
  • Convert Excel time to decimal seconds
  • How to get workdays between dates in Excel
  • DURATION function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles