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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

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)``

Yongle

Well-known Member
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
10
Views
316
Replies
7
Views
144
Replies
11
Views
121
Replies
9
Views
514
Replies
3
Views
906

1,126,896
Messages
5,621,495
Members
415,844
Latest member

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