Skip to main content

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 🙂

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'

)

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

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 🙂


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

Reply


Terms & Conditions