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).
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:
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.