Using the TODAY() Function to Filter in Excel 2007

JoeGKushner

New Member
Joined
Feb 22, 2006
Messages
11
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:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top