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

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

"Dave C" wrote:

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

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(B 1),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 A1:D1 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

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.
--
Thanks,
Shane Devenshire


"Dave C" wrote:

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

On Tue, 28 Oct 2008 15:04:05 -0700, Sheeloo <="to" & CHAR(95) & "sheeloo" &
CHAR(64) & "hotmail.com" wrote:

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

On Tue, 28 Oct 2008 17:52:00 -0700, ShaneDevenshire
wrote:

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

On Tue, 28 Oct 2008 14:48:00 -0700, Dave C
wrote:

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),MON TH(StartDt)+1,1),StartDt),
IF(DAY(EndDt+1)<1,EndDt-DAY(EndDt)+1,EndDt+1),"m")

--ron

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

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

--


Regards,


Peo Sjoblom

"TomPl" wrote in message
...
Datedif() is not featured on my version. It must be some sort of addin.

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


"Dave C" wrote:

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

My formula had the same error:
START=2/2/2008
END=3/1/2008
result: -1
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Ron Rosenfeld" wrote:

On Tue, 28 Oct 2008 14:48:00 -0700, Dave C
wrote:

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),MON TH(StartDt)+1,1),StartDt),
IF(DAY(EndDt+1)<1,EndDt-DAY(EndDt)+1,EndDt+1),"m")

--ron

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 **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"John C" wrote:

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


"Dave C" wrote:

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

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


"John C" wrote:

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 **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"John C" wrote:

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


"Dave C" wrote:

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

nevermind :)
--
** John C **


"Ron Rosenfeld" wrote:

On Tue, 28 Oct 2008 14:48:00 -0700, Dave C
wrote:

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),MON TH(StartDt)+1,1),StartDt),
IF(DAY(EndDt+1)<1,EndDt-DAY(EndDt)+1,EndDt+1),"m")

--ron

Ron Rosenfeld wrote...
On Tue, 28 Oct 2008 15:04:05 -0700, Sheeloo <="to" & CHAR(95) & "sheeloo" &
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

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.

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(E nd)))
Count of whole months
=SUMPRODUCT(--(FREQUENCY(Y08D,MoSeq)=FREQUENCY(D08D,MoSeq)))-1
=4