Joe said:
Hello,
Can anyone help me with my issues. I have a form that calculates a date
based on the end user date input. If the the user enters 11/01/09 the
formula captures 10/31/08. If the User Enter 11/30/09 the it will also
capture 10/31/09. What I need is the following. If the user enter 11/01/09
then the begin date should be 10/31/09. If the user enters 11/30/09 then the
begin date should 11/01/08.
My current formula is as follows:
=DateSerial(Year([txtEndDate]),Month([txtEndDate])-12,0)
Please help
If the first day is the only exception, try:
=DateSerial(Year([txtEndDate]),Month([txtEndDate])-12,Abs(Day([txtEndDate])>1))
11/01/09 => 10/31/08
11/02/09 => 11/01/08
....
11/30/09 => 11/01/08
To have the first n days of the month revert to the last day of the
previous month of the previous year (substitute an integer for n):
=DateSerial(Year([txtEndDate]),Month([txtEndDate])-12,Abs(Day([txtEndDate])>n))
11/01/09 => 10/31/08
11/02/09 => 10/31/08
....
11/n/09 => 10/31/08
11/n+1/09 => 11/1/08
....
11/30/09 => 11/01/08
=DateSerial(Year([txtEndDate]) -
1,Month([txtEndDate]),Abs(Day([txtEndDate])>n))
should give the same results.
James A. Fortune
(e-mail address removed)
Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular procedure,
but I am under no obligation to modify these examples to provide added
functionality or to construct procedures to meet your specific
requirements. Any code samples posted contain no known hidden material
defects. However, anyone who uses any code sample posted does so with
the understanding that they are responsible for any testing of any
illustrative code sample for any particular use. Furthermore, anyone
using an illustrative code sample I provide or code derived from it does
so at their own risk.
