Results 1 to 3 of 3

Using the TODAY() Function to Filter in Excel 2007

This is a discussion on Using the TODAY() Function to Filter in Excel 2007 within the Excel Questions forums, part of the Question Forums category; I have a report that uses three ranges of dates for gathering data. I was hoping to use TODAY() as ...

  1. #1
    New Member
    Join Date
    Feb 2006
    Posts
    9

    Default Using the TODAY() Function to Filter in Excel 2007

    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?
    Last edited by JoeGKushner; Mar 28th, 2013 at 02:20 PM. Reason: clarify

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,925

    Default Re: Using the TODAY() Function to Filter in Excel 2007

    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
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Jan 2013
    Location
    Columbus, Ohio, United States of America
    Posts
    617

    Default Re: Using the TODAY() Function to Filter in Excel 2007

    Math with Microsoft dates is hard. Include the reference to atpvbaen.xls in Tools/Reference and use (WorksheetFunction.WorkDay_Intl(today, -10, 1)):
    Code:
    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)

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com