Monthmath Error Message

Good evening everyone,

I have a field named “Date” in format mm/dd/yy and I need to create a new “Stmt Month” field based on “Date” that gives me the month in Date plus one month, i.e. if Date = 11/07/20 then “Stmt Month” must be December 2020.

I am using the following formula: datepattern(monthmath(date(“Date”),1),“Month YYYY”) but I get the error message date( function: Illegal date “Date” (month must be from 1 to 12).

What is wrong in the formula?

Thanks for your help.

Looks like your field is a date type field so the formula needs to be simplified from what you show to just use the field name without the the date( function:

datepattern(monthmath(Date,1),“Month YYYY”)

I have replaced my formula with yours and no error message this time but no result either (field is now blank)

Now I’m guessing your “Stmt Month” field is set as a date field and it should be a text field for this use.

Gary,
When it comes to PanX, you are an infinite source of wisdom.
Your help is much appreciated.
Happy holidays to you and to the community at large.

Gary,
I need to sort “Stmt Month” chronologically but since it is now a text field the sorting is alphabetical.
What is the easiest solution to solve the problem?
Thanks.

Make it a date field with an output pattern of Month YYYY and change the formula to

monthmath(month1st(Date),1)

so if Date = 11/07/20 then the date in Stmt Month will be 12/01/20 and it will display as December 2020. All the dates in Stmt Month will be the first day of their respective months.

Dave,
Exactly what I needed.
Really appreciated.