Calculate Complete Months Between Two Dates

[Dave C]

I need to calculate leavers holiday entitlement where only the number of
fully completed months are included. A month where the start date is after
the first, or a month where the end date is prior to the last day of the
month are not included.

Example: Start Date: 15 January
End Date: 26 June
Full Months: 4 (Feb; Mar; Apr; May)

The start and end dates will be entered into cells;

Is there a definhed function to calculate this or what formula would I need?

Thanks, as always, for any assistance.

Dave

 

[Sheeloo]

Try
=IF(DATEDIF(A1,B1,"m")>1,DATEDIF(A1,B1,"m")-1,0)

 

[TomPl]

Assume the start date is in cell A1.
Assume the end date is in cell B1.
Assume the full date is entered, not just day and month.

Put this formula in cell C1.
=IF(DAY(A1)=1,A1-1,A1)
Put this formula in cell D1:
=IF(B1=DATE(YEAR(B1),MONTH(B1)+1,0),B1,DATE(YEAR(B1),MONTH(B1),0))
Put this formula in cell E1:
=(YEAR(D1)-YEAR(C1))*12+MONTH(D1)-MONTH(C1)

The result should show in cell E1. Format cells A11 to date and cell E1
to number.

If I understood your question this should work properly.
If start date is the first of the month, the month is counted.
If end date is the last of the month, the month is counted.
Weekends are ignored in this process.
Formulas could be combined into one cell, but it would be a long formula.

Enjoy

 

[ShaneDevenshire]

Hi,

I think this does what you want:

=DATEDIF(A1,B1,"m")-(DAY(A1)>1)*(DAY(A1)<DAY(B1))

If it helps, please click the Yes button.

 

[Ron Rosenfeld]

Try
=IF(DATEDIF(A1,B1,"m")>1,DATEDIF(A1,B1,"m")-1,0)

A1: 1-Feb
B1: 1-Mar

--> 0

I think it should be one, as should 1-Feb --> 29-Feb
--ron

 

[Ron Rosenfeld]

Hi,

I think this does what you want:

=DATEDIF(A1,B1,"m")-(DAY(A1)>1)*(DAY(A1)<DAY(B1))

If it helps, please click the Yes button.

A1: 29-Feb
B1: 29-Sep

-->7

Mar, Apr, May, Jun, Jul, Aug is 6


--ron

 

[Ron Rosenfeld]

I need to calculate leavers holiday entitlement where only the number of
fully completed months are included. A month where the start date is after
the first, or a month where the end date is prior to the last day of the
month are not included.

Example: Start Date: 15 January
End Date: 26 June
Full Months: 4 (Feb; Mar; Apr; May)

The start and end dates will be entered into cells;

Is there a definhed function to calculate this or what formula would I need?

Thanks, as always, for any assistance.

Dave

I think this will work for you:

=DATEDIF(IF(DAY(StartDt)<>1,DATE(YEAR(StartDt),MONTH(StartDt)+1,1),StartDt),
IF(DAY(EndDt+1)<>1,EndDt-DAY(EndDt)+1,EndDt+1),"m")

--ron

 

[TomPl]

Datedif() is not featured on my version. It must be some sort of addin.

 

[Peo Sjoblom]

It is featured but there is nothing in help except in Excel 2000

--


Regards,


Peo Sjoblom

 

[John C]

Simpler formula, and meets your criteria (A1=Start Date, B1=End Date, adjust
as needed):

=DATEDIF(A1,B1,"m")-1+(DAY(A1)=1)+(DAY(B1+1)=1)

and you can even modify for a little error checking, checking to see if
either of them are blank, then don't calculate:

=IF(OR(A1="",B1=""),"",DATEDIF(A1,B1,"m")-1+(DAY(A1)=1)+(DAY(B1+1)=1))

 

[John C]

My formula had the same error:
START=2/2/2008
END=3/1/2008
result: -1

 

[John C]

small modification, as a -1 result could occur if the dates were such as
2/5/2008, 3/4/2008
=MAX(0,DATEDIF(A1,B1,"m")-1+(DAY(A1)=1)+(DAY(B1+1)=1))

 

[John C]

=MAX(0,DATEDIF(A11,B11,"m")-1*(DAY(A11)<=DAY(B11))+(DAY(A11)=1)+(DAY(B11+1)=1))

 

[John C]

nevermind
--
** John C **

I think this will work for you:

=DATEDIF(IF(DAY(StartDt)<>1,DATE(YEAR(StartDt),MONTH(StartDt)+1,1),StartDt),
IF(DAY(EndDt+1)<>1,EndDt-DAY(EndDt)+1,EndDt+1),"m")

--ron

 

[Harlan Grove]

A1:     1-Feb  
B1:     1-Mar

--> 0

I think it should be one, as should 1-Feb --> 29-Feb

An argument for using

=DATEDIF(A1,B1+1,"M")

which seems to be more reliable than

=DATEDIF(A1-1,B1,"M")

But it really seems that February confuses the @#$% out of DATEDIF.
Also begs for exact specification, e.g., if 15 Feb to 15 Mar is one
month, even though this is usually only 28 days, why isn't 15 Mar to
14 Apr, which is always 30 days, one month. Yes, I understand
comparing day of month numbers, but that means precise specification
when either beginning or ending dates' day of the month is 28 through
31.

 

[Herbert Seidenberg]

With defined names and without DATEDIF
Excel 2007
Start 01/01/08 (Y2008)
End 12/31/08 (Y2008)
Begin 01/15/08
Finish 06/26/08
D08D
=MONTH(ROW(INDEX($A:$A,Begin):INDEX($A:$A,Finish)))
Y08D
=MONTH(ROW(INDEX($A:$A,Start):INDEX($A:$A,End)))
MoSeq
=ROW(INDEX($A:$A,MONTH(Start)):INDEX($A:$A,MONTH(End)))
Count of whole months
=SUMPRODUCT(--(FREQUENCY(Y08D,MoSeq)=FREQUENCY(D08D,MoSeq)))-1
=4