How to Filter a Date Range in the Past

4/1/2020

I have a data table with over 75 columns and 20,000 rows. Column Ans contain a record date, and these cover the last five years of data. I need to filter the data to only those records with dates between 180 and 210 days ago.
A solution to this problem is to add a new column B to your data set that shows if the date in column occurs the desired timeframe. Here is the formula that I would use:

=AND(TODAY()-A2>=180,TODAY()-A2<=210)

The answer will be either True or False, depending on the date in Column A. Next, you need to set up a filter so that you only display dates in Column A where Column B is equal to True. The result will display only those rows where the dates in column A are between 180 and 210 days old.

Alan Salmon

Comments are closed.

Authors

Ward Blatch
Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at [email protected].

Tommy Stephens
Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at [email protected].

Randy Johnson
Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at [email protected]

Bernie Smith
Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

RSS Feed