Explanation
In the image shown, the current date is August 24, 2019.
Excel dates are serial numbers , so they can be manipulated with simple math operations. The TODAY function always returns the current date.
Inside the AND function , the first logical test checks to see if the date in B5 is greater than or equal to the Monday two weeks previous.
B5>=TODAY()-WEEKDAY(TODAY(),3)-14
This is based on a formula described here which gets the Monday of the current week. Once we have that date, we subtract 14 days to get the Monday two weeks prior.
The second logical test simply checks if the date is less than Monday in the current week.
B5<TODAY()-WEEKDAY(TODAY(),3)
when both results are TRUE, the AND function will return TRUE. If either result is FALSE, the AND function will return FALSE.
Last 6 weeks
The number of weeks is configurable by using an (n*7) value, where n is number of weeks. To test for the last 6 weeks, you can adjust the formula like this:
=AND(B5>=TODAY()-WEEKDAY(TODAY(),3)-42,B5<TODAY()-WEEKDAY(TODAY(),3))
Include current week
To include the current week, you can use only the first logical test:
B5>=TODAY()-WEEKDAY(TODAY(),3)-14
Note: this will include future dates (if any) that appear in source data.
Explanation
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:
="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.
With current date
To embed the current date in a string, you can use the TODAY function like this:
="Current date: "& TEXT(TODAY(), "ddd, mmmm d, yyyy")
