IIF for Criteria

Pestomania

Board Regular
Joined
May 30, 2018
Messages
145
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:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,515
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 11, 2015
Messages
6,788
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top