Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Dates - Need to display date one month prior to user-entered date

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Dates - Need to display date one month prior to user-entered date

Try

=EDATE(H5,-1)+1

It's part of the analysis toolpak


--


Regards,


Peo Sjoblom


"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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default Dates - Need to display date one month prior to user-entered date

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Dates - Need to display date one month prior to user-entered date

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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Dates - Need to display date one month prior to user-entered d

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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Dates - Need to display date one month prior to user-entered d

This does not quite work either. I think all of these date functions in
Excel take what ever month it is you are using, and simply subtract 30 days.
Thus a month with 31 days in it will yield a different result than a month
with 30 days in it (or 28, like February).

But thank you for replying!

"Peo Sjoblom" wrote:

Try

=EDATE(H5,-1)+1

It's part of the analysis toolpak


--


Regards,


Peo Sjoblom


"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.




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 623
Default Dates - Need to display date one month prior to user-entered d

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.



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Dates - Need to display date one month prior to user-entered d

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.




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Dates - Need to display date one month prior to user-entered d

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.




  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Dates - Need to display date one month prior to user-entered d

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.








  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Dates - Need to display date one month prior to user-entered d

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.






  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Dates - Need to display date one month prior to user-entered d

I was reading an Excel book on Friday and even the author was commenting on
how difficult working with dates in Excel can be.

The formula below works more consistently than any other I have tried, but
Excel still yields uneven results. Entering the end date as the 30th of the
month for all months with 31 days in them, here is what the formula yielded:

User-entered end date Formula Start Date
1) Jan-30-2008 Dec-31-2007
2) Mar-30-2008 Mar-02-2008
3) May-30-2008 May-01-2008
4) Jul-30-2008 Jul-01-2008
5) Aug-30-2008 Jul-31-2008
6) Oct-30-2008 Oct-01-2008
7) Dec-30-2008 Dec-01-2008

Below are the results I was looking for, for the one item above that did not
yield the correct results via the formula:

User-entered end date Formula Start Date
2) Mar-30-2008 Mar-01-2008

I don't know that it is possible to get better results outside of setting up
a complex table, so I will gladly go with the formula that you provided.
Thank you!


"Peo Sjoblom" wrote:

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.






  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Dates - Need to display date one month prior to user-entered d

Try this

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


it seems that the only problem you had was when the input date was between
March 29 and March 30


--


Regards,


Peo Sjoblom


"brettopp" wrote in message
...
I was reading an Excel book on Friday and even the author was commenting on
how difficult working with dates in Excel can be.

The formula below works more consistently than any other I have tried, but
Excel still yields uneven results. Entering the end date as the 30th of
the
month for all months with 31 days in them, here is what the formula
yielded:

User-entered end date Formula Start Date
1) Jan-30-2008 Dec-31-2007
2) Mar-30-2008 Mar-02-2008
3) May-30-2008 May-01-2008
4) Jul-30-2008 Jul-01-2008
5) Aug-30-2008 Jul-31-2008
6) Oct-30-2008 Oct-01-2008
7) Dec-30-2008 Dec-01-2008

Below are the results I was looking for, for the one item above that did
not
yield the correct results via the formula:

User-entered end date Formula Start Date
2) Mar-30-2008 Mar-01-2008

I don't know that it is possible to get better results outside of setting
up
a complex table, so I will gladly go with the formula that you provided.
Thank you!


"Peo Sjoblom" wrote:

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.








  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Dates - Need to display date one month prior to user-entered d

Thank you!

"Peo Sjoblom" wrote:

Try this

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


it seems that the only problem you had was when the input date was between
March 29 and March 30


--


Regards,


Peo Sjoblom


"brettopp" wrote in message
...
I was reading an Excel book on Friday and even the author was commenting on
how difficult working with dates in Excel can be.

The formula below works more consistently than any other I have tried, but
Excel still yields uneven results. Entering the end date as the 30th of
the
month for all months with 31 days in them, here is what the formula
yielded:

User-entered end date Formula Start Date
1) Jan-30-2008 Dec-31-2007
2) Mar-30-2008 Mar-02-2008
3) May-30-2008 May-01-2008
4) Jul-30-2008 Jul-01-2008
5) Aug-30-2008 Jul-31-2008
6) Oct-30-2008 Oct-01-2008
7) Dec-30-2008 Dec-01-2008

Below are the results I was looking for, for the one item above that did
not
yield the correct results via the formula:

User-entered end date Formula Start Date
2) Mar-30-2008 Mar-01-2008

I don't know that it is possible to get better results outside of setting
up
a complex table, so I will gladly go with the formula that you provided.
Thank you!


"Peo Sjoblom" wrote:

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.









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
date entered display Kate Excel Discussion (Misc queries) 7 April 11th 07 08:46 PM
incrementing a date that the user has entered? pano Excel Worksheet Functions 4 January 23rd 07 04:22 AM
Date arithmetic: adding 1 month to prior end of month date manxman Excel Worksheet Functions 2 July 14th 06 09:29 PM
dates, 1 month prior wfactor Excel Worksheet Functions 5 June 19th 06 08:23 AM
Display Last Saved By date & User? Annabelle Excel Discussion (Misc queries) 4 October 4th 05 08:27 PM


All times are GMT +1. The time now is 06:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"