Count the number of days that fall within a particular month.

[Portuga]

Hi guys,

I would like a formula or other method that would give me the amount of days that fall within a particular month based on a start date and a end date.

For example:

Start date          End Date
25/02/2008         03/03/2008

I would like to know that 5 days belong to February and 3 days to March.

Thanks in advance

[mrice]

Try this user defined function which you should paste into a blank module

Function DaysInMonth(StartDate As Date, EndDate As Date, MonthNumber As Integer)
Dim TestDate As Date
For TestDate = StartDate To EndDate
    If Month(TestDate) = MonthNumber Then DaysInMonth = DaysInMonth + 1
Next TestDate
End Function

Then call this in the sheet, for example, for March

=DaysInMonth(A1,B1,3)

[Portuga]

Excellent!

Thanks a mill mrice

[all4excel]

Portuga,

U can get an Excel Solution like this...

If your Starting Date and End Dates are in cells A2 and B2 then you can use the following formula.

[ ="There are "&(DATE(YEAR($A$2),MONTH($A$2)+1,0)-$A$2+1)& " days in the month of "&CHOOSE(MONTH($A$2),"Jan","Feb","Mar","Apr","May","June","July","Aug","Sep","Oct","Nov","Dec")&" and "& $B$2-DATE(YEAR($B$2),MONTH($B$2),1)+1&" days in the month of "& CHOOSE(MONTH($B$2),"Jan","Feb","Mar","Apr","May","June","July","Aug","Sep","Oct","Nov","Dec")&"." ].

Please let me know if this works for you.