Calculate the correct Week End Date based on Date Worked

[ewassell]

This is for payroll, I need to find the correct Saturday after date for the date that the person worked. 

If they work on January 29th, 2025 the Week Ending Date should be February 1, 2025.

if they work on January 20th, 2025 the Week Ending Date should be January 25, 2025

if they work on February 10, 2025 the Week Ending Date should be February 15, 2025.

I would like to avoid having a separate file that reads in Saturday dates if possible.

Here is what I have so far, it reads in a Week End date and a file with the information and spits out if it's a match or not.  I want this to calculate the correct Week Ending date (Saturday date right after Date Worked).

[CoG]

The following formula can be used to identify the next Saturday. FYI if the input date is a Saturday, then the output will be the same date.

DateTimeAdd([Date],6-ToNumber(DateTimeFormat([Date],"%w")),"Day")

Hope this helps and Happy Solving!

[ewassell · Accepted solution]

Thank you but I need the Saturday date for the other Work dates,  Can anyone solve without having a file that needs to be read in?

[ewassell · Accepted solution]

OH, I replied too fast.  This does work!!  I'm not understanding how the formula works though.  Does the 6 tell Alteryx that it's a Saturday?

[CoG]

The %w specifier in the DateTimeFormat() function returns the day of the week as a number beginning with Sunday as 0.

6-[that value] is the number of days until the next Saturday. For example, if the date in question is Friday then we add 6 - DateTimeFormat(Friday date, "%w") = 6 - 5 = 1 day to the date, which is obviously a Saturday.