ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Formula Logic - Set Date Range

7 - Meteor

I apologize if this has been answered in the past, but I cannot seem to find a good way to automate this formula tool.  I run a report M-F of the current week and always want to provide date ranges for prior week and next week.


If [Date] >= "2020-05-24" and [date] <= "2020-05-30" then "NEXT WEEK"
elseif [Date] >= "2020-05-17" and [date] <= "2020-05-23" then "CURRENT WEEK"
elseif [Date] >= "2020-05-10" and [date] <= "2020-05-16" then "LAST WEEK"
elseif [Date] >= "2020-05-03" and [date] <= "2020-05-09" then "PRIOR WEEK"
else Null()


Is there a way to use DateTimeToday() so I don't have to manually roll these dates each Monday?



13 - Pulsar
13 - Pulsar

Hi @kherty,


to do so, I wouyld use the week number and compare to the "today", here is a sample workflow

12 - Quasar

Hi @kherty 


The attached should work for you. I'm calculating the week start date for both the Date column and for the current week. You can then use a DateTimeDiff formula to calculate the difference in days between each week's start date and call out the appropriate week identifier.



If this solves your issue please mark the answer as correct, if not let me know!




7 - Meteor

@Ladarthure  Thanks for this!  I tried it and it appears to work well.  It did drop the Sunday of last week though.  Any idea of how I could get this included?




7 - Meteor

@Maskell_Rascal  Thanks!  This works.


I appreciate everybody's help here.