Set the date in a field to a specific date next month.

[Tpaairman]

I'm setting up a budgeting file, with each record being for a specific pay date.  The pay dates are the 5th and 20th each month.  The bills show up as a relation, so I'd like to just have a script duplicate the record, then if the date is on the 5th, change it to the 20th.  That part I've got down.  But if the date is the 20th, I need it to change the date to the 5th of the next month.

I've done this sort of thing before where I had it add 1 to Month(DATE) [something like that] and if the new month was 13, then set it to 1 and add 1 to the year.  But is there an easier way to just add one to the month, and in December, have it roll to January of the next year?

[comment]

The Date() function does this automatically for you. Try something like:

Let ( 
d = Day ( PayDate )
;
Case ( 
d = 5 ;
Date ( Month ( PayDate ) ; 20 ; Year ( PayDate ) ) ;
d = 20 ;
Date ( Month ( PayDate ) + 1 ; 5 ; Year ( PayDate ) ) 
)
)

Here, if the given PayDate falls on Dec 20, 2023, the result will be Jan 5, 2024.

Note that if the given PayDate is not the 5th or 20th of the month, the result will be empty. You can change this by adding a default result to the Case() function.