Look Who it Was Random video

Excel - Efficiently Find Dates in Excel Using AdvancedFilter | Excel Tutorial - Episode 871

1,267 views · Published 6 January 2009 · 3:52 · Indexed 8 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Efficiently Find Dates in Excel Using AdvancedFilter.

Welcome back to the MrExcel netcast, where we tackle all things Excel. In today's episode, we're going to dive into the tricky world of finding dates in Excel. As many of you may know, matching the format of dates can be a real headache. But fear not, I'm here to show you a more efficient and foolproof method for finding dates within your data set.

Have you ever recorded a Macro to find a specific date, only to have it give you debug errors when you try to run it again? This is a common frustration for many Excel users. The reason for this is that the format of your dates may have changed since you recorded the Macro, causing Excel to not find a match. But don't worry, there's a better way to approach this problem.

Instead of relying on the Macro recorder, we're going to use a completely different approach. First, we'll find the final row and column of our data set by using the N key and pressing Up. Then, we'll set up our criteria by copying the date we want to find into a separate column. Next, we'll use the powerful AdvancedFilter function, which most of us don't use in the Excel interface. This function allows us to specify our criteria and output range, and will only show us the dates that match our criteria.

The best part about using AdvancedFilter is that it's more generic and user-friendly than the traditional Edit Find method. It looks at the underlying value of the data, rather than the format, so it will work every single time. Plus, if the date we're looking for isn't in the data set, it won't give us an error, it will simply show us a blank workbook. So say goodbye to frustrating debug errors and hello to a more efficient way of finding dates in Excel.

I hope you found this tutorial helpful. Don't forget to subscribe to our channel for more Excel tips and tricks. Thanks for watching and we'll see you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction
(00:18) The Challenge of Finding Dates in Excel
(00:28) The Macro Recorder and Debug Errors
(01:16) A Different Approach: Using AdvancedFilter
(01:41) Setting Up Criteria and Output Ranges
(02:22) Using the AdvancedFilter Function
(03:10) Benefits of Using AdvancedFilter
(03:26) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Excel AdvancedFilter function
Excel criteria range
Excel data filtering
Excel data set analysis
Excel date formatting
Excel date matching
Excel find formatted numbers
Excel output range
Finding dates in Excel
Finding specific dates in Excel
Macro debug errors
Using AdvancedFilter in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1151995/



Someone tried to record a macro to find the records from a certain date. Since finding dates requires the date format to match exactly, a better way to go would be using Advanced Filter. In Episode 871, I show how to use AdvancedFilter in a macro to extract records from a certain date.

More from this channel