Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

How to show last updated date stamp in Excel

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.

Also See:   Convert date to text 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")

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.

Also See:   Get month name from date in Excel

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.

Also See:   How to join date and text together in Excel

Leave a Reply

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