work out 30 days end of month

[Guest]

Im trying to get a formula that will work out the the due date on an invoice
based on 30 days, end of month.
Im assuming it needs to be something along the lines of Invoice Date + 30 +
difference between the last day of the month and invoice date. i would like
it to work out the last day of the month for me if this is possible. Im new
to databases so please be gentle, im learning from a dummies guide

 

[raskew via AccessMonster.com]

Nicki -

The following will return the last day a given month, taking into
consideration leap years:

x = "02/2007"
? dateserial(year(x), month(x)+ 1, 0)
2/28/07

y = "02/2008"
? dateserial(year(y), month(y)+ 1, 0)
2/29/08

Bob

 

[Guest]

You can do the whole thing with a single expression calling the DateSerial
function:

=DateSerial(Year([InvoiceDate]),Month([InvoiceDate])+1,30)

You might think at first sight that if the invoice month is December then
because Month([InvoiceDate])+1 would be 13 this would not work, but in fact
this is correctly interpreted as January of the next year. Similarly, if the
invoice month is January putting 30 as the day argument simply pushes the
date returned forward into March, either 1st or 2nd depending on whether the
year is a leap year or not.

Using zero as the day argument will give you the last day of the invoice
date's month as Bob explained.

Ken Sheridan
Stafford, England