extract month from date

[novice1]

hi,

i have following formula build into my Alteryx workflow, that always looks at the data for current month and it would pull back data for previous month is today is 1st of the current month. 

if DateTimeDay(DateTimeToday()) = 1
then DateTimeAdd(DateTimeAdd(DateTimeToday(),[Date today]*-1,"days"),-1,"month")
else DateTimeAdd(DateTimeToday(),(DateTimeDay(DateTimeToday())-1)*-1,"days") endif

I am struggling to modify the above formula to account for the fact that when i run report on Sunday (and if said sunday is in the new month) how can i get data for previous month with the cut off day previous friday?

what i am trying to do is to get sales data previous month if sunday is in 2nd November, and look at the current month if sunday is on any other day of the current month

[messi007]

Hello,

You can check first if the day is Sunday and it's the first of the month formula below

DateTimeFormat([DateField],"%A") = "Sunday" and DateTimeDay(DateTimeToday()) = 1

 then you update your formula with -2

else your formula.

Hope that will help,

Regards,

[novice1]

hi, 

can you please explain a bit more.

currently i have formula set as 

formula to workout date for yesterday

DateTimeDay(DateTimeToday())-1

and then a formula that extracts 1st day of the current month or previous month if today is 1st of the month

if DateTimeDay(DateTimeToday()) = 1
then DateTimeAdd(DateTimeAdd(DateTimeToday(),[Date today]*-1,"days"),-1,"month")
else DateTimeAdd(DateTimeToday(),(DateTimeDay(DateTimeToday())-1)*-1,"days") endif

So, if the day that i am running report is 2nd of the month (sunday for example), how do i have to amend the above formula so that it will look up data for previous month up to and inluding friday 

[novice1]

i have attached part of my workflow 

so if i run this today i need to see result as 2020-10-01

[messi007]

Hello,

please see below the formula:

Hope that helps!

Regards

[messi007]

Hello,

Attached the correct solution

Hope that Helps 🙂

Regards

[novice1]

thank you. 

i need this to be automated so that i dont need to update todays date. 

i have tried to apply formula to my workflow, but getting an error message. 

i am quite new to this and still learning, so any help is much appreciated

[messi007]

Dear @novice1,

The formula I shared with you is automated all based in today date automatically.

I'm not sure that I understand what you mean by automated.

Could you please share the formula with the error and I will check.

Many thanks in advance,

Regards

[novice1]

hi,

Thank you so much. It works ok now.

Am I correct in assuming that this formula will always pull 1st of the current month and will only replace 1st of the current month with 1st of the previous month if today is 1st of the month?

Regards,

[messi007 · Accepted solution]

Hello,

Yes correct, and if the first day of the month is Sunday it will give you the first day of the previous month.

Please mark my answer as accepted as a solution with a like if you don't mind 🙂

Happy to help!

Regards,