calculating months between dates

[wendy]

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

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

can anyone help I am using datedif to work out the months but I onl
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 i
advance for any help on this

 

[Fred Smith]

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

Regards,
Fred

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[smartin]

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

 

[wendy]

;3455255']How about:
=datedif(a1,a2,"m")+(a2=eomonth(a2,0))

Regards,
Fred

"wendy" (e-mail address removed) 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 wha
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 a
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 th
month.
The list contains various end dates like the example above. Thank
in
advance for any help on this x

thankyou for your help