Help

Is between Monday and Sunday of previous week

Topic Labels: Formulas
Solved
Jump to Solution
1010 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Mustafa_B
5 - Automation Enthusiast
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},
DATEADD(TODAY(),-8,'days')),
IS_BEFORE(
{Completed Date},
DATEADD(TODAY(),-0,'days')
)
),
'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},
DATEADD(TODAY(),
-8*(WEEKDAY(TODAY(),
'Monday')+1),'day')
),
IS_BEFORE(
{Completed Date},
DATEADD(TODAY(),
0-WEEKDAY(TODAY(),
'Monday'),'day')
)
),
'Yes','No'
)

I Hope someone can help me out :slightly_smiling_face:

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
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")

Screen Shot 2021-10-12 at 9.18.42 PM

See Solution in Thread

3 Replies 3
Mustafa_B
5 - Automation Enthusiast
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'
)
Justin_Barrett
18 - Pluto
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")

Screen Shot 2021-10-12 at 9.18.42 PM

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!!