Explanation
In this example, the goal is to create a formula that will return TRUE if a date is in the last complete 6 month period, starting in the previous month. This means the date must fall between a calculated start date and end date, which requires two logical tests. The formula uses the AND function to require that both logical tests are TRUE. In the example shown, the current date is October 30, 2021. The formula in D5, copied down, is:
=AND(B5>EOMONTH(TODAY(),-7),B5<=EOMONTH(TODAY(),-1))
Excel dates are serial numbers , so you can manipulate them with simple math operations. The TODAY function returns the current date on an on-going basis. Inside the AND function , the first logical test checks to see if the date in B5 is greater than the last day of the month 7 months previous to the current date:
=B5>EOMONTH(TODAY(),-7) // test 1
We use the EOMONTH function to move back in time to the last day of the month 7 months previous the current date, which is calculated with the TODAY function .
The second logical test checks if the date is less than or equal to the last day of the previous month:
B5<=EOMONTH(TODAY(),-1) // test 2
when both results are TRUE, the AND function will return TRUE. If either result is FALSE, the AND function will return FALSE.
Last 12 months
To test for the last 12 months, you can adjust the formula like this:
=AND(B5>EOMONTH(TODAY(),-13),B5<=EOMONTH(TODAY(),-1))
Return custom value
This formula can be combined with the IF function to return any value you want. For example, to return “Last 6” when a date is within 6 months, you can use:
=IF(AND(B5>EOMONTH(TODAY(),-7),B5<=EOMONTH(TODAY(),-1)),"Last 6", "")
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.
