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.
Related formulas
Last n days
Last n months
Related functions
AND Function
The Excel AND function is a logical function used to test multiple conditions at the same time. AND returns TRUE only if all the conditions are met. If any conditions are not met, the AND function returns FALSE. The AND function is commonly used with other...
TODAY Function
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 with a date number format. If you need current date and time, use...
WEEKDAY Function
The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.
