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 text "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.
You can embed the date in any format you like, using the codes that represent date formats (dd, mm ,yyyy, etc.)
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:
Dates and times in Excel are stored as serial numbers and converted to human readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. In a similar way, the TEXT...
The TEXT function accepts a number as the first argument, something called "format_text" as the second. The format_text argument works like a number format, and specifies the formatting that should be used when converting the number to text. You can...
The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format. If you need current...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.