View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TomPl TomPl is offline
external usenet poster
 
Posts: 342
Default Calculate Complete Months Between Two Dates

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