Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

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

DanC
Moderator
Moderator
Created

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 ofeach 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
deepu
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. 

RodL
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.

 

deepu
5 - Atom

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

Nicholas_White
8 - Asteroid

Hey,

 

Chiming in here to say 'thanks', I used the formula as part of a date-masking solution.

 

vitaminman12_0-1665771443281.png

 

- Nick