Here's a guess

=IF(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)),DATE (YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))+1)



--


Regards,


Peo Sjoblom


"brettopp" wrote in message
...
Below are examples of correct start dates that should be calculated based
on
the user-entered end date. I have listed a month with 31 days in it
(January), 28-29 days in it (February), and 30 days in it (April):

User-entered end date Correct start date
Jan-16-2008 Dec-17-2007
Feb-16-2008 Jan-17-2008 (leap and non-leap year both)
Apr-16-2008 Mar-17-2008

Jan-31-2008 Jan-01-2007
Feb-28-2008 Feb-01-2008 (non-leap year)
Feb-29-2008 Feb-01-2008 (leap year)
Apr-30-2008 Apr-01-2008

Jan-01-2008 Dec-02-2007
Feb-01-2008 Jan-02-2008 (leap and non-leap year both)
Apr-01-2008 Mar-02-2008

Hope this helps.


"Fred Smith" wrote:

This problem is solvable, Brett, but we need to know what solution you
want.
You've told us what you don't want, but not what you do want. For each of
the
examples where the formula calculated the wrong result, tell us what the
right
result is. For example, what's wrong with the Dec 31/07 start date?

--
Regards,
Fred


"brettopp" wrote in message
...
Thanks, Elkar. This ALMOST works. I was checking various dates for
those
months with 31 days in them. If I enter the 5th, 15th, or 31st of the
month,
the formula works great. But for some reason, if I enter the 30th of
the
month, the results are again inconsistent.

Entering the 30th of the month for Jan, Mar, May, Jul, Aug, Oct, and
Dec, I
get the following results (the first column is the date I entered, the
second
is the result using the formula):

End Start
Jan-30-2008 Dec-31-2007
Mar-30-2008 Mar-02-2008
May-30-2008 May-01-2008
Jul-30-2008 Jul-01-2008
Aug-30-2008 Jul-31-2008
Oct-30-2008 Oct-01-2008
Dec-30-2008 Dec-01-2008

But thank you for responding!


"Elkar" wrote:

Try this:

=DATE(YEAR(H5+1),MONTH(H5+1)-1,DAY(H5+1))

HTH,
Elkar


"brettopp" wrote:

I'm looking for a formula that displays the billing-month start date
based
on
a user-entered end date. The system I will be using this
calculation for
considers billing month periods as ending one month less one day
after the
billing month start date. For example, if my billing month starts
on April
1, it ends on April 30. If starts on May 1, it ends May 31. If
starts on
April 15, it ends on May 14. If starts on February 28, it ends on
March 27
(non-leap year). If starts on February 29 (leap year), it ends on
March
28.

For example, if a user enters an end date of 31-JAN-08, the billing
month
start date that the formula will calculate should show 01-JAN-08.
If the
user enters an end date of 15-JAN-08, the billing month start date
the
formula will calculate should show 16-DEC-07.

I thought a very easy way to do this would be using the DATE
function:
=DATE(YEAR(H5),MONTH(H5)-1,DAY(H5)), where H5 is the cell the user
enters
the
end date into.

Below are the results I achieved using this formula, which are all
over the
place. I have included here only months that have 31 days in them
to show
how this formula yields different results even on months with the
same
number
of days. The first column lists the user-entered end date I entered
into
Excel, and the second columns shows the results given by the DATE
formula I
listed above.

User-entered end date Billing-month start date
Jan-31-2008 Dec-31-2007
Mar-31-2008 Mar-02-2008
May-31-2008 May-01-2008
Jul-31-2008 Jul-01-2008
Aug-31-2008 Jul-31-2008
Oct-31-2008 Oct-01-2008
Dec-31-2008 Dec-01-2008

Only the months of May, July, October, and December yielded the
correct
billing-month start date (which starts on the first of the month in
this
example).

The results using this formula were equally chaotic for months with
only 30
days in them.

Once last thing to note: In my sample dates above I used the last
day of
the month as the user entered end date in order to give a consistent
example.
In practice, the user-entered end date can be any day of the month,
not
just
the last day of the month.