EOMONTH to compare two dates (Excel 2003)

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

 

[Peo Sjoblom]

How does your real formula look?



--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[sithjanitor]

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