calculating months between dates
 

I have looked through some older posts and thought I had found the
answer to my problem I'm nearly there but not quite!

The start date identifies the start month. It doesn’t matter what date
in the month it is. The end date month only counts if the end date is
the last day of the month i.e. 10 May to 29 June would only count as 1
month for May. 10 May to 30 June would count as 2 months because it
includes the last day of the month.

can anyone help I am using datedif to work out the months but I only
want it to count the end date month if it is the last day of the month.
The list contains various end dates like the example above. Thanks in
advance for any help on this x




--
wendy

calculating months between dates
 

How about:
=datedif(a1,a2,"m")+(a2=eomonth(a2,0))

Regards,
Fred

"wendy" wrote in message
...

I have looked through some older posts and thought I had found the
answer to my problem I'm nearly there but not quite!

The start date identifies the start month. It doesn't matter what date
in the month it is. The end date month only counts if the end date is
the last day of the month i.e. 10 May to 29 June would only count as 1
month for May. 10 May to 30 June would count as 2 months because it
includes the last day of the month.

can anyone help I am using datedif to work out the months but I only
want it to count the end date month if it is the last day of the month.
The list contains various end dates like the example above. Thanks in
advance for any help on this x




--
wendy

calculating months between dates
 

On Tue, 25 Aug 2009 22:06:41 +0100, wendy
wrote:


I have looked through some older posts and thought I had found the
answer to my problem I'm nearly there but not quite!

The start date identifies the start month. It doesn’t matter what date
in the month it is. The end date month only counts if the end date is
the last day of the month i.e. 10 May to 29 June would only count as 1
month for May. 10 May to 30 June would count as 2 months because it
includes the last day of the month.

can anyone help I am using datedif to work out the months but I only
want it to count the end date month if it is the last day of the month.
The list contains various end dates like the example above. Thanks in
advance for any help on this x

If I understand your requirements,
you ALWAYS will count the first month
you count the last month only if it includes the last day of the month.

Try this:

=DATEDIF(StartDate+1-DAY(StartDate),EndDate+2-DAY(EndDate+1),"m")

You do need to be careful using DATEDIF. It is an unsupported function and at
least one feature ("md") was broken in Excel 2007 SP2 and later.

An alternative formula, that I think should work, would be:

=(YEAR(EndDate+2-DAY(EndDate+1))-YEAR(StartDate))*12+
MONTH(EndDate+2-DAY(EndDate+1))-MONTH(StartDate)

--ron

calculating months between dates
 

On Tue, 25 Aug 2009 22:17:48 -0400, Ron Rosenfeld
wrote:

On Tue, 25 Aug 2009 22:06:41 +0100, wendy
wrote:


I have looked through some older posts and thought I had found the
answer to my problem I'm nearly there but not quite!

The start date identifies the start month. It doesn’t matter what date
in the month it is. The end date month only counts if the end date is
the last day of the month i.e. 10 May to 29 June would only count as 1
month for May. 10 May to 30 June would count as 2 months because it
includes the last day of the month.

can anyone help I am using datedif to work out the months but I only
want it to count the end date month if it is the last day of the month.
The list contains various end dates like the example above. Thanks in
advance for any help on this x

If I understand your requirements,
you ALWAYS will count the first month
you count the last month only if it includes the last day of the month.

Try this:

=DATEDIF(StartDate+1-DAY(StartDate),EndDate+2-DAY(EndDate+1),"m")

You do need to be careful using DATEDIF. It is an unsupported function and at
least one feature ("md") was broken in Excel 2007 SP2 and later.

An alternative formula, that I think should work, would be:

=(YEAR(EndDate+2-DAY(EndDate+1))-YEAR(StartDate))*12+
MONTH(EndDate+2-DAY(EndDate+1))-MONTH(StartDate)

--ron

I just noted an inconsistency.

You indicate that you want a month to count only if it includes the last day of
the month. You imply you want the first month to count regardless.

If both rules are true, rule takes precedence? In other words, if the two
dates are in the same month, by the EndDate is prior to the last day of the
month, should this count as 1 month or 0 months?

If both rules are not true, then what is the rule for counting StartDate as a
month?
--ron

calculating months between dates
 

wendy wrote:
I have looked through some older posts and thought I had found the
answer to my problem I'm nearly there but not quite!

The start date identifies the start month. It doesn’t matter what date
in the month it is. The end date month only counts if the end date is
the last day of the month i.e. 10 May to 29 June would only count as 1
month for May. 10 May to 30 June would count as 2 months because it
includes the last day of the month.

can anyone help I am using datedif to work out the months but I only
want it to count the end date month if it is the last day of the month.
The list contains various end dates like the example above. Thanks in
advance for any help on this x

Assuming "Start Date" in A2 and "End Date" in B2:

=DATEDIF(A2,EOMONTH(B2+1,0),"m")+(A2=EOMONTH(A2,0) )

Or possibly (not sure why I thought of this):

=MAX(1,DATEDIF(A2,EOMONTH(B2+1,0),"m")+(A2=EOMONTH (A2,0)))

calculating months between dates
 

'Fred Smith[_4_ Wrote:
;3455255']How about:
=datedif(a1,a2,"m")+(a2=eomonth(a2,0))

Regards,
Fred

"wendy" wrote in message
...-

I have looked through some older posts and thought I had found the
answer to my problem I'm nearly there but not quite!

The start date identifies the start month. It doesn't matter what
date
in the month it is. The end date month only counts if the end date is
the last day of the month i.e. 10 May to 29 June would only count as
1
month for May. 10 May to 30 June would count as 2 months because it
includes the last day of the month.

can anyone help I am using datedif to work out the months but I only
want it to count the end date month if it is the last day of the
month.
The list contains various end dates like the example above. Thanks
in
advance for any help on this x




--
wendy -
thankyou for your help x




--
wendy