Excel - number of whole months between 2 days - but not starting on the 1st

[pendle666]

Hello

I'm working on some backdated payments for people. However, our pay month doesn't start until 16th of each month.

So I need to work out the number of whole pay periods (16th - 15th) between, say, 16/03/14 and 15/11/14 which should be 7.

I've got formulae all over the spreadsheet and would prefer to use a formula in this too.



thank you for helping

____________
Pendle

 

[SkipVought]

Hi,

So what about MyDate-15 for every date in your calculation?

 

[combo]

First calculate difference between full months. Next adjust days. If first date's day is less or equal to 16 and second date's day is greater or equal to 15 do nothing, otherwise deduct 1:
=12*(YEAR(Date2)-YEAR(Date1))+(MONTH(Date2)-MONTH(Date1))+IF(AND(DAY(Date1)<=16,DAY(Date2)>=15),0,-1)

combo

 

[pendle666]

Thanks both - I shall try these tomorrow.

thank you for helping

____________
Pendle