Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom AutoFilter to Compare Due Dates to Current Date
Column D of my spreadsheet contains due dates. I need a macro that will
activate the Custom AutoFilter and compare the due dates to the current date. The macro should then display only those entries where the due date is less than or equal to the current date. I tried using =TODAY() in the Custom AutoFilter dialog box. However, the system does not retrieve any "hits." In searching this forum, I came across the following code: & CLng(Date) and used it in my macro as follows: Sub ApplyDateStatusFilter() ' Filters by Due Date (displays any entry that matches current date or any date prior to current date) Selection.AutoFilter Field:=4, Criteria1:="<=" & CLng(Date), Operator:=xlAnd End Sub The macro works -- but I don't understand what the & CLng(Date) does in the macro. Can someone help? Is there a better/different way to achieve the desired result? Thank you!! Cristina |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom AutoFilter to Compare Due Dates to Current Date
Ron de Bruin sent me this message in a private email a few months ago:
See also Stephen his Autofilter notes in this PDF http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behaviour. The criteria string consists of an operator (=, , <, = etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales. There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of "=02/01/2001" will find all dates on or after 1st Feb, 2001, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1st Feb, 2001 and will work in any locale: Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001" Cristina wrote: Column D of my spreadsheet contains due dates. I need a macro that will activate the Custom AutoFilter and compare the due dates to the current date. The macro should then display only those entries where the due date is less than or equal to the current date. I tried using =TODAY() in the Custom AutoFilter dialog box. However, the system does not retrieve any "hits." In searching this forum, I came across the following code: & CLng(Date) and used it in my macro as follows: Sub ApplyDateStatusFilter() ' Filters by Due Date (displays any entry that matches current date or any date prior to current date) Selection.AutoFilter Field:=4, Criteria1:="<=" & CLng(Date), Operator:=xlAnd End Sub The macro works -- but I don't understand what the & CLng(Date) does in the macro. Can someone help? Is there a better/different way to achieve the desired result? Thank you!! Cristina -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom format date cells-Autofilter doesn't work right | Excel Discussion (Misc queries) | |||
How do I get the most current date from a list of dates? | Excel Discussion (Misc queries) | |||
Compare current date to 2 dates | Excel Programming | |||
Compare Current Date | Excel Programming | |||
Custom Date in Autofilter | Excel Programming |