Exceljet

Quick, clean, and to the point

Last n weeks

Excel formula: Last n weeks
Generic formula 
=AND(A1>=TODAY()-WEEKDAY(TODAY(),3)-(n*7),A1<TODAY()-WEEKDAY(TODAY(),3))
Explanation 

To check if a date is within the last n weeks of today's date, you can use a formula based on the AND, TODAY, and WEEKDAY functions. In the example shown, we are testing for dates in the last 2 weeks. The formula in C5, copied down, is:

=AND(B5>=TODAY()-WEEKDAY(TODAY(),3)-14,B5<TODAY()-WEEKDAY(TODAY(),3))

The result is TRUE for any date in the last complete 2 week period, where weeks begin on Monday. The TODAY function will return the current date on an ongoing basis, so this formula can be used create reports to show a rolling 4 weeks, rolling 6 weeks, etc.

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

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.