IIF for Criteria

Pestomania

Board Regular
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:

Code:
``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.

Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this for the Criteria:
Code:
``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)``

Code:
``````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``````

Replies
6
Views
336
Replies
3
Views
562
Replies
6
Views
192
Replies
10
Views
771
Replies
5
Views
2K

1,203,465
Messages
6,055,576
Members
444,799
Latest member
CraigCrowhurst

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.

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

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