Tom’s Tutorials For Excel: Filtering Dates
When it comes to filtering dates, a little VBA goes a long way in dealing with the nemesis of seemingly countless different formats a date can be represented in Excel.
In the picture, an obnoxious myriad of dates is shown in the Date column to demonstrate how, no matter how users enter or format their dates, you can deal with it using the below macros.
This first macro named FilterBetweenDates does what the above picture shows, which is to filter a table based on a pair of Before Date and After Date criteria.
Sub FilterBetweenDates()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim StartDate As Date, EndDate As Date
Dim FilterStartDate As Date, FilterEndDate As Date
Dim LastRow As Long
Dim FilterRange As Range
StartDate = Range("B5").Value
EndDate = Range("B6").Value
LastRow = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("A8:A" & LastRow)
FilterStartDate = _
DateSerial(Year(StartDate), Month(StartDate), Day(StartDate) - 1)
FilterEndDate = _
DateSerial(Year(EndDate), Month(EndDate), Day(EndDate) + 1)
FilterRange.AutoFilter _
Field:=1, Criteria1:=">" & CDbl(FilterStartDate), _
Operator:=xlAnd, _
Criteria2:="<" & CDbl(FilterEndDate)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub
This macro filters for dates before today's date:
Sub FilterDateBeforeToday()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LastRow As Long, FilterRange As Range
LastRow = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("A8:A" & LastRow)
FilterRange.AutoFilter Field:=1, Criteria1:="<" & CDbl(Date)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub
This macro filters for dates after today's date:
Sub FilterDateAfterToday()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LastRow As Long, FilterRange As Range
LastRow = Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("A8:A" & LastRow)
FilterRange.AutoFilter Field:=1, Criteria1:=">" & CDbl(Date)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub
This macro deletes rows whose filtered dates are older than 3 years ago from today:
Sub DeleteRows3YearsOld()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim FilterRange As Range, myDate As Date
myDate = DateSerial(Year(Date) - 3, Month(Date), Day(Date))
Set FilterRange = _
Range("A8:A" & Cells(Rows.Count, 1).End(xlUp).Row)
FilterRange.AutoFilter Field:=1, Criteria1:="<" & CDbl(myDate)
On Error Resume Next
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
End With
Err.Clear
Set FilterRange = Nothing
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
Tom, my browser is Google Chrome and when i copy code from your pages, line breaks are not preserved. Can you somehow publish the code with line breaks kept? Thank you very much!
When you say line breaks are not preserved, do you mean that the entire code is on one single line? Or instead is the underscore line continuation breaks the problem? If you email me I can send a workbook example. Chrome is usually good but I have heard people mention how their browsers see things differently on a web page.
Yep, one single line when copying from Chrome. Frome example when copying from this very page. And OK when copying from MS Internet Explorer. The reare case when MSIE beats Chrome. ))))
Thanks. I’ll look forward to comments from other Chrome users who see this; so far you are the first I have heard of it but I will keep my eye on it.
Hi
I used the code in the “This macro filters for dates before today’s date” section and it worked great – thanks for that!
I also want my macro to (separately) look at rows with a date of today though. Unfortunately, I can’t figure out how to customise the criteria to do this though. Could you point me in the right direction please?
Also, because I would be using both sections of code in the same macro, I’m finding that it errors if I duplicate the first few lines. Is it safe to remove these or should I do it slightly differently?
Thanks in advance.
Mark
Hi Mark, thanks for following my Excel examples.
A quick one-liner to filter today’s date can be this, if for example your dates are in column H (and H being the 8th letter of the alphabet, with the table starting in column A):
Range(“A1:H100”).AutoFilter Field:=8, Criteria1:=Date
A longer catch-all version would be to filter between dates, that is, to filter for any single date or between a pair of dates. So, for a single date, you would enter that date in both Y1 and Z1 of this example, for dates in column P.
Sub FilterBetweenDates()
‘In this example you enter the from date in cell Y1 and the end date in cell Z1.
Dim LR&, FilterRange As Range
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim StartDate As Date, EndDate As Date
Dim ClosedWindowStartDate As Date
Dim ClosedWindowEndDate As Date
StartDate = Range(“Y1”).Value
EndDate = Range(“Z1″).Value
LR = Cells.Find(What:=”*”, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range(“P1:P” & LR)
ClosedWindowStartDate = DateSerial(Year(StartDate), Month(StartDate), Day(StartDate) – 1)
ClosedWindowEndDate = DateSerial(Year(EndDate), Month(EndDate), Day(EndDate) + 1)
FilterRange.AutoFilter _
Field:=1, Criteria1:=”>” & CDbl(ClosedWindowStartDate), _
Operator:=xlAnd, _
Criteria2:=”<" & CDbl(ClosedWindowEndDate)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub
If you are trying to duplicate the code lines that contain the variable declarations, you cannot do that. Declare variables uniquely depending on what you are doing. Now, if you have two or more macros in the same module and you want to declare a variable for each one but you don't want to keep repeating that action, instead of putting the Dim declarations inside the macro, you can enter this for example, outside of and at the top of all macros in your module:
Public LR&
Public FilterRange As Range
Hi
Thanks for the quick reply. Didn’t quite understand what you meant to get around having it twice in the same macro so I added it like this:
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Range(“$A$1:$AW$5000″).AutoFilter Field:=11, Criteria1:=Date
Set FilterRange = Nothing
Application.ScreenUpdating = True
This seems to work ok except that the date is in the wrong format – when I manually check the filter details, it’s put it in US format. What is puzzling me though is that the original code (below) does it in the right format.
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LastRow As Long, FilterRange As Range
LastRow = _
Cells.Find(What:=”*”, After:=Range(“K1”), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range(“K1:K” & LastRow)
FilterRange.AutoFilter Field:=11, Criteria1:=”<" & CDbl(Date)
Set FilterRange = Nothing
Application.ScreenUpdating = True
Any ideas?
Thanks
Mark
I’m lost on your question. When you wrote…
“I also want my macro to (separately) look at rows with a date of today though.”
…I interpreted that to mean you want to filter a table of data to show only rows with today’s date.
How about if you re-state your question by saying what you want to do. Leave out the part of whatever should be duplicated or not included or not working, and don’t worry about the data format. Just say what data you have, and what you want it to end up like after you have (presumably) filtered it. Be sure to mention relevant information, such as the first column of data (column A or whatever it is), the column holding the date (column X or whatever it is), the row holding the header labels, and the row where the actual data starts (which should be one row below the header labels but I am covering my bases to make sure I can envision where your table is located). From that point we can decide what code works best for you.
Hi Tom
Sorry about that 🙂
I have a spreadsheet with headers in Row 1 and the data starts in Row 2.
Column K contains dates that I need to format with different colours – red if the date is older than today and orange if the date is today and green if the date is in the future.
I’m doing this in a macro as there is a bunch of other formatting needed and this is something I have to do on a daily basis with a completely new set of data each day so Conditional Formatting isn’t really an option.
Hope this provides a clear picture.
Cheers
Mark
Oh, sorry the data starts in Column A 🙂
Not needing an exact number because I know the count of rows will change every day which is no problem, but still asking in order top recommend an optimal solution, approximately how many rows of data is this table.
Hi Tom
I account for 5000 in other parts of the code but realistically, no more than 1000.
Thanks
Mark
See if this does what you want:
Sub ColorDates()Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LastRow As Long, FilterRange As Range
Dim myDate As Date
Dim StartDate As Date, EndDate As Date
LastRow = Cells(Rows.Count, 11).End(xlUp).Row
Range("K2:K" & LastRow).Interior.ColorIndex = xlNone
Set FilterRange = Range("K1:K" & LastRow)
myDate = VBA.Date
StartDate = DateSerial(Year(myDate), Month(myDate), Day(myDate) - 1)
EndDate = DateSerial(Year(myDate), Month(myDate), Day(myDate) + 1)
'Today
FilterRange.AutoFilter _
Field:=1, Criteria1:=">" & CDbl(StartDate), _
Operator:=xlAnd, _
Criteria2:="<" & CDbl(EndDate)
On Error Resume Next
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Interior.Color = RGB(255, 165, 0)
End With
Err.Clear
'Past
FilterRange.AutoFilter Field:=1, Criteria1:="" & CDbl(myDate)
On Error Resume Next
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Interior.Color = vbGreen
End With
Err.Clear
ActiveSheet.AutoFilterMode = False
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub
Hi Tom
When I run this and pause it at the point that the filter is applied and look at the criteria being used, it says “is after 14/10/2013” and “is before 16/10/2013”. This appears to include the 14/10 (previous) as well as the 15/10 (current) but it’s only the current date that should be coloured orange.
The next filter appears to have “41562” as the value being filtered, which obviously doesn’t give any results.
The end result is that both the previous (should be coloured red) and current day (should be coloured orange) are coloured orange, anything older than 14/10 (should be red) is not coloured and anything from 16/10 onwards (should be green) isn’t coloured.
Cheers
Mark
The code worked when I tested it based on this you wrote:
“Column K contains dates that I need to format with different colours – red if the date is older than today and orange if the date is today and green if the date is in the future.”
The previous day is colored red, the current date is colored orange, and the future dates are colored green. I just tested it again right now with new data.
Make sure there are no merged cells.
Test it yourself again, and if you till see previous dates in column K colored orange and not red, send me your workbook.
Hi
I’m afraid it’s definitely not working as expected but I can’t really send you the sheet as it contains sensitive information but below are some examples of the end result:
08/10/2013 – No Fill
14/10/2013 – Orange
15/10/2013 – Orange
16/10/2013 – No Fill
I’m not sure if it has any effect but I should probably point out that the values themselves also have times (e.g. 08/10/2013 11:10) but this is true for even the ones that are successfully filled. Also, the macro is hosted in a separate workbook to the one containing the data.
Hope this helps.
Cheers
Mark
There’s no reason I can think of as to why you are seeing what you are seeing. If you can send me a worksheet with those column K dates copied and pasted, that would suffice for the actual data you are wannting to filter. Something else is going on with those cells or the workbook. The format of the date is not important, **IF** those cells are truly dates and not text.
I’ve just checked the format and they are in Custom – dd/mm/yyyy hh:mm format. Would that cause the problem? How can I get the file to you?
Both questions you asked in your last comment were addressed in my last comment.
Hi ,
I tried to follow but got a bit confusion
How do I write the VBA for macros to autofilter 5 days before today?
Thanks,
Ziyan
This will work, assuming column A. Modify for actual column.
Sub Test2()
'Filter only dates that are 5 days before today.
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim FilterRangeDate As Range
Set FilterRangeDate = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Dim ClosedWindowStartDate As Date, ClosedWindowEndDate As Date
ClosedWindowStartDate = DateSerial(Year(Date), Month(Date), Day(Date) - 6)
ClosedWindowEndDate = DateSerial(Year(Date), Month(Date), Day(Date) - 4)
FilterRangeDate.AutoFilter _
Field:=1, Criteria1:=">" & CDbl(ClosedWindowStartDate), _
Operator:=xlAnd, _
Criteria2:="<" & CDbl(ClosedWindowEndDate) Set FilterRangeDate = Nothing Application.ScreenUpdating = True End Sub
i would like to filter two different dates at a time like,
3-April-16 and 5-April-16. then what will be the code in VBA.
NB: Date comes from a reference which is changeable.
Hi,
If i need to put filter for todays date what is the macro code?
Thanks,
You can use the first macro to filter between dates. The criteria for the start date is =today()-1 and for the end date it is =today()+1 if you were putting those criteria into cells such as the example shows.
If you don’t want to use cells to hold your criteria, you can code them directly using dateserial, example
DateSerial(Year(VBA.date), month(VBA.date), day(VBA.date)-1) for the start date variable and +1 for the end date variable.
HI Tom,
Thanks for the update and it really works, but check the details scenario below
I used the code below
Sub FilterBetweenDates()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim StartDate As Date, EndDate As Date
Dim FilterStartDate As Date, FilterEndDate As Date
Dim LastRow As Long
Dim FilterRange As Range
StartDate = Range(“B3”).Value
EndDate = Range(“B10″).Value
LastRow = _
Cells.Find(What:=”*”, After:=Range(“B2”), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range(“B2:B” & LastRow)
FilterStartDate = _
DateSerial(Year(VBA.Date), Month(VBA.Date), Day(VBA.Date) – 1)
FilterEndDate = _
DateSerial(Year(VBA.Date), Month(VBA.Date), Day(VBA.Date) + 1)
FilterRange.AutoFilter _
Field:=1, Criteria1:=”>” & CDbl(FilterStartDate), _
Operator:=xlAnd, _
Criteria2:=”<" & CDbl(FilterEndDate)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub
——————————
this particular works when i enter the function called today() in the one particular cell.
Example: I have a serious of dates in one format and one particular cell alone I enter the function today(). In this scenario the output comes.
But the macro i am expecting is, it has to get the system date automatcially and I need the output like the date will have the format (DD/MM/YYYY). I might enter the manual date also and later change the date format to DD/MM/YYYY. So the macro should run and filter the output as today date.
Dump of the Excel Example with cell number:
Date (B2)
26/06/1992 (DD/MM/YYYY) (B3)
16/03/1992 (DD/MM/YYYY) (B4)
30/03/1992 (DD/MM/YYYY) (B5)
03/03/1992 (DD/MM/YYYY) (B6)
26/07/2016 (DD/MM/YYYY) (B7)
26/02/1992 (DD/MM/YYYY) (B8)
26/08/1992 (DD/MM/YYYY) (B9)
27/06/2016 (=today()) – one particular cell has the function (B10)
If I am not clear in explaining the scenario let me know I will give a hangout call in mobile.
My whatapp number : +91 9840032452 (Country India)
I do not understand why you cannot do what I suggested in my previous reply, to get the date directly from the system instead of using a cell, examples with the variables I first posted:
StartDate = DateSerial(Year(VBA.date), month(VBA.date), day(VBA.date)-1)
EndDate = DateSerial(Year(VBA.date), month(VBA.date), day(VBA.date)+1)
Regarding the format of the dates in the output, you can format them in the macro (modify my code as needed) to appear on the worksheet any way you want to see them for the final results.
Hi Tom,
I have sent the excel file to your email, please check the code and correct if anything wrong.
Your dates were text, not real dates.
When you change them to real dates (not just so they *look* like dates, but really *are* dates, this macro works, given your example dataset:
Sub FilterToday()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim FilterRangeDate As Range
Set FilterRangeDate = Range(“B2:B” & Cells(Rows.Count, 2).End(xlUp).Row)
Dim StartDate As Date, EndDate As Date
StartDate = DateSerial(Year(Date), Month(Date), Day(Date) – 1)
EndDate = DateSerial(Year(Date), Month(Date), Day(Date) + 1)
FilterRangeDate.AutoFilter _
Field:=1, Criteria1:=”>” & CDbl(StartDate), _
Operator:=xlAnd, _
Criteria2:=”<" & CDbl(EndDate) Set FilterRangeDate = Nothing Application.ScreenUpdating = True End Sub
Hi Tom,
I need to filter the date formats, My scenario is to filter the date formats is most latest date to previous days it may be 1 day, 2 days, 3 days,4 days, 5 days like that up to 7 days.for that i need to create macro. Can help me for the code.please reply for it
Thanks