Exceljet

Quick, clean, and to the point

Last n months

Excel formula: Last n months
Generic formula 
=AND(B5>=EOMONTH(TODAY(),-(n+1))+1,B5<EOMONTH(TODAY(),-1))
Explanation 

To check if a date is within the last n months of today's date, you can use a formula based on the AND, TODAY, and EOMONTH functions. In the example shown, we are checking for dates in the last 6 months. The formula in D5, copied down, is:

=AND(B5>=EOMONTH(TODAY(),-7)+1,B5<EOMONTH(TODAY(),-1))

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.

How this formula works

In the image shown, the current date is August 23, 2019.

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 or equal to the first day of the month 6 months previous

=AND(B5>=EOMONTH(TODAY(),-7)+1

We use the EOMONTH function to move back in time to the last day of the month 7 months earlier, then add 1 to end up at the first day of the next month.

The second logical test checks if the date is less than the last day of the previous month:

B5<EOMONTH(TODAY(),-1)

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)+1,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)+1,B5<EOMONTH(TODAY(),-1)),"Last 6", "")
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.