Get value based on current week

[keeprollin]

I have a column which contains the date when a product is being bought. I want to create a new column which would give me a yes or a no if a user buys a new product (starting date) in the current week. To simplify my statement - How can I dynamically get a Yes if the starting date falls in the current week? The current week should start on Monday and end on Sunday.  I'm providing a sample dataset below.

Starting date	Current Week
12/31/2021	No
10/6/2021	No
12/17/2021	No
11/3/2021	No
1/21/2022	No
1/20/2022	No
1/30/2022	No
1/31/2022	Yes
2/1/2022	Yes
2/2/2022	Yes
2/3/2022	Yes
2/4/2022	Yes
2/5/2022	Yes
2/6/2022	Yes

In this case, I have used the following formula:  

if DateTimeDiff([Starting date],DateTimeToday(),'days') >= -2

and DateTimeDiff([Starting date],DateTimeToday(),'days') <=4

THEN "Yes"

ELSE

"No"

ENDIF

However, the issue is that let's assume that the starting date has its values as '2/5/2022' and '2/6/2022' and today's date is '2/7/2022', then also the current week column will give Yes, even though 2/5/2022 would be last week. Can someone please help me solve this problem statement? 

[AngelosPachis · Accepted solution]

Hi @keeprollin ,

What you can try instead is to get the week number from your dates in the [Starting date] field. To do that you will have to use a Datetime function that returns the week number when the first day of the week is number 

DateTimeFormat([Date formatted],"%W")

Then you can do the same with today's date and compare the two numbers with an if statement, as shown below:

Hope that helps,

Angelos

[binuacs]

@keeprollin similar to @AngelosPachis approach

IIF(DateTimeFormat(DateTimeParse([Starting date],'%m/%d/%Y'),"%W") == DateTimeFormat(DateTimeNow(),"%W"), 'Yes','No')