Skip to main content
Solved

Is between Monday and Sunday of previous week

  • October 12, 2021
  • 3 replies
  • 44 views
M

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:

Best answer by Justin_Barrett

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

M
  • Mustafa_B
  • Author
  • New Participant
  • October 12, 2021

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'
)
Kamille_Parks11
Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • Answer
  • October 13, 2021

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

M
M
  • Mustafa_B
  • Author
  • New Participant
  • October 13, 2021

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


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

Justin_Barrett
Terms & ConditionsAccessibility statement