alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## How to calculate the last sunday of month March and Last sunday of Month October

5 - Atom

Hope you all doing well. I am new to Alteryx designer.

I am stuck while designing the workflow where the requirement is I want to calculate the date of last Sunday of the month of March and October irrespective of the year.

For Example, For the year 2020

March: 29-03-2020 (Sunday)

October: 25-10-2020 (Sunday)

For Example, For the Year 2021

March: 28-03-2020 (Sunday)

October: 31-10-2020 (Sunday)

So you can see data is changing, I want to calculate the date dynamically.

Thanks,

Ayub Khan

7 REPLIES 7
7 - Meteor

Hopefully this gives you an idea and starting point, I am sure there are other ways but this is what we do without using a 2nd helper table.

22 - Nova

Here is a formula for the task. I am just taking the weekday of first of next month and adjusting the dates

-iif(ToNumber(
DateTimeFormat(
,"%w"))=0,7,
-ToNumber(
DateTimeFormat(
,"%w"))),
"days")

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

20 - Arcturus

@AyubKhan
How about a more straightforward way/

5 - Atom

Hi @atcodedog5

Thanks for the response.

One question here if I want to run this workflow dynamically. For example

when it runs for 2020, it should only give me the output date for the last Sunday of March and the Last Sunday of October for 2020

when 2021 starts the from 1st fo Jan it should give me the output date for the last Sunday of March and Last Sunday of October for 2021.

I do not want to hard code the input values.

Thanks,

22 - Nova

Sure thats possible. You can give the month and get year from todays date

Here is formula to get the current year.

DateTimeYear(DateTimeToday())

I am just giving month its picking up the year.

Output:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

22 - Nova

Happy to help 🙂 @AyubKhan

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

20 - Arcturus

If you like it mark it as accept please😂
multiple accept is allowed.

Labels