community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

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

Moderator
Moderator
Created on

Question

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

Answer

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!

Attachments
Comments
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.

DateTimeParse([Date],"%m/%d/%Y")

 

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.

 

Atom

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