Alteryx doesn’t have a built in function to calculate the first day of the week for a given date. Below are a couple formulas you can use to convert a date to the first day of the week.
Converting a date to the first day of the week
In Summary: Use the datetimeadd() function to subtract the weekday number (%w) from the date in your data to calculate the first day of the week.
In Detail: You are determining the day of the week of your date as a number (ex: Sunday = 0, Monday = 1, Tuesday = 2, etc) by using the datetimeformat() function with the “%w” specifier. Then you are taking the date from your data, and subtracting the newly calculated week day number from it by using the datetimeadd() function. The end result is the first day of the week corresponding to the date in your data.
To have your week start on Sunday, use the following:
To have your week start on Monday, add an additional day:
You can see these two formulas in action in the attachment here: First Day of Week Alteryx Workflow
There are many other ways to calculate the first day of the week, but this is the one I typically use. Let us know in the comments of any additional approaches that you take!
This is a huge time saver! Thank you.
This is a great solution for a static first day of a week, but what if I want end date? Also, what should I do if my start or end date needs to be dynamic. Please help!
so helpful! thank you!