Oct 11, 2021 09:15 PM
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:
Solved! Go to Solution.
Oct 12, 2021 09:19 PM
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")
Oct 12, 2021 02:43 AM
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'
)
Oct 12, 2021 09:19 PM
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")
Oct 12, 2021 10:37 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!!