A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

work out 30 days end of month



 
 
Thread Tools Display Modes
  #1  
Old February 13th, 2007, 03:52 PM posted to microsoft.public.access
nicki
external usenet poster
 
Posts: 9
Default 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

Ads
  #2  
Old February 13th, 2007, 04:36 PM posted to microsoft.public.access
raskew via AccessMonster.com
external usenet poster
 
Posts: 370
Default 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  
Old February 13th, 2007, 05:32 PM posted to microsoft.public.access
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.