Explanation
In the image shown, the current date is August 19, 2019.
Excel dates are serial numbers, so you can manipulate them 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 today's date minus 7 days:
=B5>=(TODAY()-7)<TODAY())
The second logical test checks if the date is less than today:
B5<TODAY()
when both results are TRUE, the AND function will return TRUE. If either result is FALSE, the AND function will return FALSE.
Without future checks
The second test is meant to exclude any dates greater than (or equal to) today. This test only makes sense if data may include dates in the future, for example forecasts or estimates. If there are no future dates, or if you want future dates included, the formula can be simplified to:
=B5>=(TODAY()-7)<TODAY())
Return custom value
This formula can be combined with the IF function to return any value you want. For example, to return "Last 7" when a date is within last 7 days, and nothing if not, you can use:
=IF(AND(B5>=(TODAY()-7),B5<TODAY()),"Last 7", "")
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...
