The result is TRUE for any date in the last complete six month period, starting with the previous month. The TODAY function will continue to return the current date, so you can use a formula like this to create reports based on a rolling 6 months, rolling 12 months, etc. 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: 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: 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:
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:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
