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
="Last update: "&TEXT(date,"date format")
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.
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.