Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music

How to Find the Next Specified Day from a List of Dates



If I have a list of dates, how can I find the date of the next Monday for each date?


Using a simple workflow, you can calculate the next Monday from any date by using a single Formula tool and configuring as follows:

  1. Determine the day of each date
    Day: DateTimeFormat([Sample Dates],"%a")

  2. Calculate the number of days to get to the next Monday based on each day
    AddDays: Switch([Day],Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1)

  3. Add the number of days (from step 2) to get to the next Monday to each date
    Monday: DateTimeAdd([Sample Dates],[AddDays],"days")

  4. Verify that new date is Monday
    VerifyNewDay: DateTimeFormat([Monday],"%a")

You can actually do all of this within one formula, save for verifying the day, if you want to get fancy:

Monday: DateTimeAdd([Sample Dates], Switch(DateTimeFormat([Sample Dates],"%a"),Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1),"days")


Things to consider:

This workflow assumes that your dates are already in the Alteryx native date format of "YYYY-MM-DD". If they aren't, please visit another Knowledge Base article, Date Conversions, for tips on how to get your dates there!


This was done in 10.1. Sample workflow attached.


Thanks for tuning in!

5 - Atom

I am finding difficult to add a one year and a day to my date field. To be more clear my current date is 01/26/2017, I want the result as 01/27/2018. Please could you let me know how can this be acheived. 

Alteryx Alumni (Retired)

FYI...better to ask these questions in a discussion topic so that people can attach examples.


In general, you would first convert to a Date data type using the DateTimeParse() function.



Then you do the date math with DateTimeAdd().

You could just add 366 days, but that wouldn't account for leap years, so I would nest two DateTimeAdd() functions with the first adding a year and then another to add a day.

DateTimeAdd(DateTimeAdd([DateFormated],1,"year"), 1, "day")


And of course you can nest all of the above into one expression.


5 - Atom

Thanks a lot Rodl, I will do it going forward. I worked !!!. thanks a lot for the quick help.