Using the TODAY() Function to Filter in Excel 2007

[JoeGKushner]

I have a report that uses three ranges of dates for gathering data. I was hoping to use TODAY() as part of a filter but when I try and use it, say, After or Equal to TODAY()-10, it shows no records. If I then put in After or Equal to 3/18/2013 (today's date), then I have records. Any help?

 

[Jonmo1]

I don't think you can use any functions in the filter criteria.

You might consider a helper column, and put a formula
=A1>=TODAY()-10

This will give True/False
Then filter that column for TRUE

 

[James Snyder]

Math with Microsoft dates is hard. Include the reference to atpvbaen.xls in Tools/Reference and use (WorksheetFunction.WorkDay_Intl(today, -10, 1)):

Private Function FileDate(inDate As String) As String
    ' Declarations and Definitions
    Dim today As Date
    Dim yestDate As String
    Dim yestHour As String
    Dim yestMinute As String
    Dim yestSecond As String
    Dim dateLen As Integer
    
    If inDate = "" Then         ' If it doesn't already exist, make it
        dateLen = 14
        today = Now()
        yestDate = Format(WorksheetFunction.WorkDay_Intl(today, -1, 1), _
            "yyyymmddHhNnSs")
        yestDate = Left(yestDate, 8)
        yestHour = CStr(Hour(today))
        yestMinute = CStr(Minute(today))
        yestSecond = CStr(Second(today))
        yestDate = yestDate & yestHour & yestMinute & yestSecond
        If Len(yestDate) <> dateLen Then    ' Account for trimmed '0'
            yestDate = yestDate & "0"
        End If
        
        FileDate = yestDate     ' Return the static date for yesterday
    Else
        FileDate = inDate       ' Return a useable date regardless
    End If
End Function

Another option is to use the serial number Microsoft represents dates as and do subtraction from that.
WorksheetFunction.WorkDay_Intl Method (Excel)