The problem is not that, it is because you haven't explained the problem
enough. Why for instance do you only have dates with the last day of the
month and only the 31st in your example?

However you don't show what the date should be if the input date is January
5th or March 1st for instance?


--


Regards,


Peo Sjoblom


"brettopp" wrote in message
...
This formula does not quite work, either. Below is a table showing
user-entered end dates, and the resulting start dates yielded by the
formula:

User-entered end date Formula Start Date
Jan-31-2008 Jan-01-2008
Mar-31-2008 Mar-03-2008
May-31-2008 May-02-2008
Jul-31-2008 Jul-02-2008
Aug-31-2008 Aug-01-2008
Oct-31-2008 Oct-02-2008
Dec-31-2008 Dec-02-2008

The correct start dates in all of these cases should fall on the 1st of
the
month. So January and August are the only months where the formula gave
the
correct start date. Which is odd since ALL of these months have 31 days
in
them.

"T. Valko" wrote:

I'm not sure, but try this:

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

If that doesn't work give us several examples of the entered date and the
expected result date. Include examples using dates in February with both
leap years and non leap years since Feb always messes up date formulas!

--
Biff
Microsoft Excel MVP


"brettopp" wrote in message
...
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.