Counting specific dates between date range

[Seacucumber]

Hi - I am trying to work out how much interest I have to pay for a loan I have. The interest gets applied every month on the 2nd.

I want to be able to input

Cell A1: First date for example 14/06/2011
Cell B1: Second date for example 04/08/2011

Cell C1: Result 2

Hope this makes sense; I have been banging my head on the table for the last two days trying to figure it out!

 

[Sektor]

=DATEDIF(A1,B1,"m")

You won't have result as 2 'cause there aren't full two months between two dates.

 

[Seacucumber]

Thanks for the reply.

But I am not looking to count the months, I am looking to count the instances of the 2nd of each month.

The formula as you quite rightly point out will bring the volume back as one.

I need it to equal two.

Thanks

 

[Sektor]

Change "less than" to "<"

=DATEDIF(A1;B1;"m")+IF(DAY(B1) "less than" DAY(A1);1;0)

 

[Seacucumber]

Thanks again, tried that formula and it doesn't have the desired effect.

For example 01/01/2011 and 03/02/2011 - returns one and not two.

 

[Seacucumber]

Sorted added +1 to the outcome.

Think this is resolved, thanks

 

[T. Valko]

Thanks for the reply.

But I am not looking to count the months, I am looking to count the instances of the 2nd of each month.

The formula as you quite rightly point out will bring the volume back as one.

I need it to equal two.

Thanks

Try this...

A1 = start date
B1 = end date

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=2))

 

[T. Valko]

Try this...

A1 = start date
B1 = end date

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=2))

Here's a non-volatile method...

=12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)+1-(DAY(A1)>2)-(DAY(B1)<2)

 

[Seacucumber]

Thank you T. Valko that seems to work.

 

[T. Valko]

Thank you T. Valko that seems to work.

You're welcome. Thanks for the feedback!