Return value of "yes" if date is on the Monday to Friday of the previous week

[junkforhr]

Hi,

I have a column with various dates in it (column AK).

Scenario:

Today's date (Tuesday 13/09/2022) if date in AK2 is between Monday 05/09/2022 and Friday 09/09/2022, then return the value of "Yes", otherwise "No".

So next week the Monday's date would be 19/09/2022 and the previous Monday would be 12/09/2022 and Friday would be 16/09/2022. so the previous values of "yes" above would then be "No".

I hope I've been clear.

Cheers
Haydn

 

[bebo021999]

Where to put the formula?
Not sure I can understand 100% but below is some guide:
Current Monday of current week:
=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,0,1,2,3,4,5)
Then: Last Monday of previous week:
=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,0,1,2,3,4,5)-7
and last Friday of previous week:
=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,0,1,2,3,4,5)-3

Then formula should be:

=IF(AND(AK2>=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,0,1,2,3,4,5)-7,AK2<=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,0,1,2,3,4,5)-3),"Yes","No")

 

[kevin9999]

This formula's no better than @bebo021999 provided in post #2, just a bit shorter.

=IF(AND(AK2>=TODAY()-3-WEEKDAY(TODAY(),15),AK2<=TODAY()+1-WEEKDAY(TODAY(),15)),"Yes","No")

 

[Trebor76]

Another similar formula to kevin9999 and based on the solution from here:

=IF(AND(AK2>=TODAY()-WEEKDAY(TODAY())-5,AK2<=TODAY()-WEEKDAY(TODAY())-5+4),"Yes","No")