Go Back

Last updated date stamp

Read time: 25 minutes

In this tutorial, you will learn how to get last updated date stamp using TEXT function and ampersand operator “&”. To be able to set last updated date stamp you will have to concatenate text “Last updated: “ and date from the relevant cell. To join date and text you must use TEXT function because dates have numeric formats in Excel. TEXT function is converting date from number to text, and you can define a date in different date formats.

Last updated date stamp

Formula

="Last update: "&TEXT(date,"date format")

Explanation

To get the last updated date stamp you will have to use TEXT function and ampersand operator “&” for joining text and date. Let’s first explain TEXT function. This function converts a date to text since the date has a numeric format in Excel. The formula contains two arguments; value, which is in our formula date and format_text which is in our formula date format. Date formats are always under quotation enabling you to format date as it is suitable for you. If you want to get the name of the day, just put under quotations “dddd”. For a shorter version of the name of the day date format will look like “ddd”.

To join date and text use ampersand operator “&”. Text should always be under quotations, except for formulas that are part of the concatenation process. You should take care of the spaces in the text since ampersand operator “&” is joining text without spaces.

Example 1

The idea in the example below is to get the last date stamp. We can do this with TEXT excel function in combination with ampersand operator to join text “Last update: “ with the date in column “Date”. The formula in the cell C3 looks like:

="Last update: "&TEXT(B3,"dddd, mmmm yyyy")

 

Figure 1. Last updated date stamp

As we have mentioned in Explanation part, TEXT function converts the date in the cell B3 from the number to the text format. Date format “dddd, mmmm yyyy” will display day name, month name and the year of the date from the cell B3. The first part of the formula is the text under quotations “Last update: “ and it is linked with TEXT formula with ampersand operator “&”. The result of the formula will be: “Last update: Wednesday, January 2018”.

To manipulate with last updated date more easily, you can define it as the named range. In the name box of the date cell, you just have to name the cell.  The formula for the date defined as the named range will look like:

= "Last update: "&TEXT(last_update,"dddd, mmmm yyyy")

The formula has the same logic as the previous formula. The only difference is in the date that we defined as named range last_update.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need VBA coding to help apply a date stamp when a cell is updated / changed. There are multiple cells which may change and multiple date stamp columns
Solved by S. Q. in 17 mins
I have a vba code to know the last saved time stamp and the last user who saved it. Unfortunately, it does not update whenever I save the file even closing and re-opening it. It only gets updated when you double click the cell where the formula is and press enter then it gets updated. The sheet name is "Status" and the formulas are on cell M2 and Q2. Please help.
Solved by C. C. in 17 mins
need vba code to help with date stamp
Solved by X. C. in 16 mins

Leave a Comment

avatar