If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
work out 30 days end of month
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 |
#2
|
|||
|
|||
work out 30 days end of month
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 Nicki wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200702/1 |
#3
|
|||
|
|||
work out 30 days end of month
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 "Nicki" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|