Formula which calculates the last day of the month

[WasTB]

I'm working with CR Standard v11.
I have an Invoice Table, which gives me an invoice date. What I want to do is calculate the payment due date, but rather than 30 days from Invoice Date, it needs to be the last day of the following month.
For instance; if the Invoice Date is 19 March 2010, the due date should be 30 April 2010, and if the Invoice Date is 3 February 2010, the due date should be 28 February 2010.

The payment terms will range from 30 days through to 120 days, but I reckon if I can get the basic formula, I can work out the rest.

 

[lbass]

Your sample dates don't follow your own logic. Anyway, use a formula like this:

dateserial(year({table.invdate}),month({table.invdate})+2,1)-1

-LB

 

[IanWaterman]

YOur feb data does not make sense, surely due date is 31 March 2010.

Easiest way to determine last day of month is to subtract 1 day from first of following month.

Formula below adds 2 months to invoice date, determins fits of that month and subtracts 1 day.

@duedate

Date(year(dateadd("m", 2, invoicedate)), month(dateadd("m", 2, invoicedate)), 01)-1

Ian

 

[WasTB]

Oops..... you're right, (maybe I got a bit carried away with the question). But thanks for that, it works perfectly.