First Day of next month

Hi Friends,

I am running alteryx workflows based on the dates. i have a scenario, where i want 1st day of next month. for example, if my current date is 09/24/2016 then i need the next month date as 10/01/2016. Please help me to solve this issue,

Regards,

Aysha

Workflow

Date Time

Accepted answers

JohnJPS

Hi @aysha6029

The following will get the first day of the next month for given date field [Date]:

DateTimeAdd(
   DateTimeAdd([Date],1,"months"),
   1 - ToNumber(DateTimeFormat([Date],"%d")),"days")

Basically, it adds one month, then subtracts back based on the day of the month.

All comments

JohnJPS

Hi @aysha6029

The following will get the first day of the next month for given date field [Date]:

DateTimeAdd(
   DateTimeAdd([Date],1,"months"),
   1 - ToNumber(DateTimeFormat([Date],"%d")),"days")

Basically, it adds one month, then subtracts back based on the day of the month.

aysha6029

Thank You. It is working.

Dcurwenreed

Hi John,

I've tried implementing your solution below but i am getting a weird occurrence. For some dates it is working, but for others it isn't. From what i can see the ones it is failing on are those which are the 31st of the month, but again, it isn't failing on all of them, just some.

Success:

* 2017-12-31

* 2018-07-31

* 2016-07-31

Fails:

* 2018-03-31

* 2016-10-31

* 2016-03-31

(This list isn't exhaustive) but looking at the rest of it, it seems that months of 31 days that are followed by a month with 31 says (July & December) work, but months that aren't (Jan, March, May, October) don't work if the number is the 31st of the month that is being modified.

Is there a solution to this?

Thanks

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703