IIF for Criteria

[Pestomania]

Hi! I am trying to use an IIF to determine the day of the week. And then it should return different dates. Here is my problem:

If today is Monday, return Monday - Friday of last week. If today is not Monday, return Monday - Now of this week. Below is the code I have tried using:

IIF ( WEEKDAY(NOW())=2, (NOW()-7 AND NOW()-6 AND NOW()-5 AND NOW()-4 AND NOW()-3), (NOW()+8-WEEKDAY(NOW())-6 AND NOW()+8-WEEKDAY(NOW())-5 AND NOW()+8-WEEKDAY(NOW())-4 AND NOW()+8-WEEKDAY(NOW())-3 AND NOW()+8-WEEKDAY(NOW())-2))

If I am making this more difficult than necessary, please let me know.

 

[Joe4]

Try this for the Criteria:

Between IIf(Weekday(Date(),2)=1,Date()-7,Date()-Weekday(Date(),2)+1) and IIf(Weekday(Date(),2)=1,Date()-3,Date()-Weekday(Date(),2)+5)

 

[Yongle]

Sub GetDates()
    Dim D As Date, fromDate As Date, toDate As Date, aDate As Date, msg As String
    D = Date
'range of dates
    fromDate = IIf(Weekday(D) = 2, D - 7, D - ((D - 2) Mod 7))
    toDate = IIf(Weekday(D) = 2, D - 3, D)
'get values
    msg = fromDate & " - " & toDate & vbCr & vbCr
        For aDate = fromDate To toDate
            msg = msg & vbCr & aDate
        Next
    MsgBox msg
End Sub