EOMONTH to compare two dates (Excel 2003)

  • Thread starter Thread starter sithjanitor
  • Start date Start date
S

sithjanitor

I have two sets of dates. The benchmark is the first FULL month after the
first date. Thus, if the first date occurs on the first of the month - the
starting month counts as the first month. Otherwise, the first month is the
month following the start. (i.e. - if the first date is 5/1/yy and the
second is 5/15/yy, May is the first month. Otherwise, any other date in May
causes June to be the first month).

I have the Analysis Toolpak installed and have used EOMONTH by itself. It
works fine. Generates dates, evaluates logically, etc, BUT ONLY ON ONE SIDE
OF AN EQUATION.

The formula I have tried produces a #NAME? error
=IF(AND(DAY({date1})>1,EOMONTH({date1},0)=EOMONTH({date2},0)),{thishappens},{thathappens})

I produced this formula in the formula editor. The EOMONTH function
evaluates correctly ({date1} of 5/5/yy evaluates to 5/31/yy). The DAY
function evaluates correctly. The IF function evaluates to #NAME? at the
logical_test. The AND function evaluates to #NAME? at the logical2
condition. The formula editor result is blank, as is my experience when
there is some type of problem in the formula. However, the cell displays the
{thishappens} (ie -TRUE) outcome.

I realize this is lengthy, but the formula makes sense to me but the result
does not. I wanted the knowledgeable expert to be able to easily duplicate
my efforts.

Any suggestions would be great, even if it works around the EOMONTH function.
 
I'm not sure if you can make use of this or not, but given {date1}, this
formula formula will calculate the first day of the full month on (if the
1st of the month) or after {date1}...

DATE(YEAR({date1}),MONTH({date1})+(DAY({date1})>1),1)

So, if I read your formula's intention correctly, I **think** you can use
this instead....

=IF(MONTH(DATE(YEAR({date1}),MONTH({date1})+(DAY({date1})>1),1))=MONTH({date2}),{thathappens},{thishappens})

Note the reversal of your {thishappens} and {thathappens}.

Rick
 
I have two sets of dates. The benchmark is the first FULL month after the
first date. Thus, if the first date occurs on the first of the month - the
starting month counts as the first month. Otherwise, the first month is the
month following the start. (i.e. - if the first date is 5/1/yy and the
second is 5/15/yy, May is the first month. Otherwise, any other date in May
causes June to be the first month).

I have the Analysis Toolpak installed and have used EOMONTH by itself. It
works fine. Generates dates, evaluates logically, etc, BUT ONLY ON ONE SIDE
OF AN EQUATION.

The formula I have tried produces a #NAME? error:
=IF(AND(DAY({date1})>1,EOMONTH({date1},0)=EOMONTH({date2},0)),{thishappens},{thathappens})

I produced this formula in the formula editor. The EOMONTH function
evaluates correctly ({date1} of 5/5/yy evaluates to 5/31/yy). The DAY
function evaluates correctly. The IF function evaluates to #NAME? at the
logical_test. The AND function evaluates to #NAME? at the logical2
condition. The formula editor result is blank, as is my experience when
there is some type of problem in the formula. However, the cell displays the
{thishappens} (ie -TRUE) outcome.

I realize this is lengthy, but the formula makes sense to me but the result
does not. I wanted the knowledgeable expert to be able to easily duplicate
my efforts.

Any suggestions would be great, even if it works around the EOMONTH function.
Click to expand...

I don't understand exactly what you are trying to do. But to generate the
first of a month, if date1 is on the first; and the first of the month, if
date1 is later than the first, you can use the formula:

=EOMONTH(date1,-1+(DAY(date1)>1))+1

or

--ron
 
Let me retry this:
Two things have to happen:
1) Item is placed in service/made available - first month's availability is
determined as of the end OF THE FIRST FULL month. So, item made availabe
5/1/08 is available for the FULL month of May, thus May counts. If made
available the 2nd, 5th, 25th or 30th, June becomes the first FULL month
available.
2) Item is leased/purchased/put into use - subject to the above, this is
simply determined as of the end of the month leased. Thus, leased 5/31/08 is
leased in May because it is leased as of the end of May.

So, a unit leased 5/25/08 would not count the month of May unless it was
placed in service 5/1/08. Although leased at the end of May, it had not been
a full month since placed in service. So it's first month would be June.

This is the formula I started with:
=IF(AND(DAY(D43)>1,EOMONTH(D43,0)=EOMONTH(E43,0)),0,1)
column D is Dates Placed in Service
column E is Dates Put into Use/Leased

Thus, if the DAY in D43 is higher than 1, AND the EOMONTH of E43 is the same
as D43, result is 0 - don't count the month. This is because the month
placed in service is the same as the month leased, but the unit has not been
in service a FULL month (DAY is greater than 1). Otherwise, count the month
(result = 1).
 
Back
Top