Quick, clean, and to the point

Last n days

Excel formula: Last n days
Generic formula 

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

You can use a formula like this for conditional formatting, or to filter data in a Pivot Table.

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:


The second logical test checks if the date is less than 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:


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", "")
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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.