Working with Dates

[Rhino373]

I'm at the end of my programming and I need help working with dates. My data is organized by date. A button is available to look at yesterday's data. This is simply programmed as Now() -1.

In this case on Monday it would not show me what I want to see as it would filter for Sunday where nothing occured. I need a function to say if Now() represents Monday then Now() - 3.

How do I extrapolate the day of the week from date?

My whole function looks like this:

Function ViewJournalEntries()
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$AN$10").AutoFilter Field:=1, Criteria1:=Array( _
    "xxxxxxx", "xxxxxxx", "xxxxxxx"), Operator:=xlFilterValues
ActiveSheet.Range("$A$2:$AN$10").AutoFilter Field:=39, Operator:= _
    xlFilterValues, Criteria2:=Array(2, Now())
End Function

 

[Rhino373]

In this example I am just filtering for Now() not Now() - 1 just because its not working like I expect it to.