# Is between Monday and Sunday of previous week

Topic Labels: Formulas
Solved
1183 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi!

I’m trying to display ‘Yes’ on records that fall between Monday and Sunday of the Previous week, no matter what day of the week it currently is. My code works, but only if it you’re looking at it on the Monday of this week, otherwise it’s off.

``````IF(
AND(
IS_AFTER(
{Completed Date},
IS_BEFORE(
{Completed Date},
)
),
'Yes','No'
)
``````

I’ve also tried this, but again it has to be run on the Monday of the current week to be accurate…

``````IF(
AND(
IS_AFTER(
{Completed Date},
-8*(WEEKDAY(TODAY(),
'Monday')+1),'day')
),
IS_BEFORE(
{Completed Date},
0-WEEKDAY(TODAY(),
'Monday'),'day')
)
),
'Yes','No'
)
``````

I Hope someone can help me out :slightly_smiling_face:

1 Solution

Accepted Solutions
18 - Pluto

Welcome to the community, @Mustafa_B! :grinning_face_with_big_eyes: There’s a much easier way of doing this using the `WEEKNUM()` function, which returns the week number of any given date. It defaults to using Sunday as the first day of the week, but you can optionally pass a second argument to force it to treat Monday as the start of the week. With that, your formula reduces to this:

``````IF(WEEKNUM({Completed Date}, "Monday") = WEEKNUM(NOW(), "Monday") - 1, "Yes", "No")
``````

3 Replies 3
5 - Automation Enthusiast

I think I may have figured it out (with the help of some other threads). I am not sure if it’s full proof yet, but I will post it now so that it will either help someone else, or someone else can correct any mistakes I’ve made.

``````IF(
AND(
IS_AFTER(
{Completed Date},

SWITCH(
WEEKDAY(TODAY()),
1, DATEADD(TODAY(), -8, 'days'),
2, DATEADD(TODAY(), -9, 'days'),
3, DATEADD(TODAY(), -10, 'days'),
4, DATEADD(TODAY(), -11, 'days'),
5, DATEADD(TODAY(), -12, 'days'),
6, DATEADD(TODAY(), -13, 'days'),
0, DATEADD(TODAY(), -14, 'days')
)),

IS_BEFORE(
{Completed Date},

SWITCH(
WEEKDAY(TODAY()),
1, DATEADD(TODAY(), -2, 'days'),
2, DATEADD(TODAY(), -1, 'days'),
3, DATEADD(TODAY(), -3, 'days'),
4, DATEADD(TODAY(), -4, 'days'),
5, DATEADD(TODAY(), -5, 'days'),
6, DATEADD(TODAY(), -6, 'days'),
0, DATEADD(TODAY(), -7, 'days')

))),
'Yes','No'
)
``````
18 - Pluto

Welcome to the community, @Mustafa_B! :grinning_face_with_big_eyes: There’s a much easier way of doing this using the `WEEKNUM()` function, which returns the week number of any given date. It defaults to using Sunday as the first day of the week, but you can optionally pass a second argument to force it to treat Monday as the start of the week. With that, your formula reduces to this:

``````IF(WEEKNUM({Completed Date}, "Monday") = WEEKNUM(NOW(), "Monday") - 1, "Yes", "No")
``````

5 - Automation Enthusiast

Hi @Justin_Barrett ! Happy to be part of the community :slightly_smiling_face:

Your solution works great, I will be replacing my code, thank you very much!!