Months in 2 dates

[zwaz]

Hi

Is there a way to identify the number od days between 2 dates and the month

Example

01/01/08 - 15/03/08

Answer = Jan = 31 days, Feb - 29 days, March = 15 days

Any help would be greatly appreciated

 

[xld]

This will give you a value for each of the 12 months if entered in a cell and copied down 12 cells in a column

=SUMPRODUCT(--(MONTH(ROW(INDIRECT($A$1&":"&$B$1)))=ROW(A1)))

If you want it in a row, change ROW to COLUMN.

 

[northwolves]

Try the following codes:

Sub macro1()
MsgBox getall(#1/1/2008#, #9/15/2008#)
End Sub
Function getall(ByVal d1 As Date, ByVal d2 As Date) As String
Dim s() As String, i As Long
ReDim s(Month(d1) To Month(d2))
For i = LBound(s) To UBound(s)
s(i) = Format(DateSerial(Year(d1), i + 1, 0), "mmm=d") & " days"
Next
s(LBound(s)) = Format(d1, "mmm=") & DateSerial(Year(d1), Month(d1) + 1, 0) - d1 & " days"
s(UBound(s)) = Format(d2, "mmm=d") & " days"
getall = Join(s, ",")
End Function

Regards
Northwolves

 

[barry houdini]

If your start date is in A2 and end date in B2 then you could list the 1st of each month in D1 across, e.g. D1 has 1-Jan-2008, E1 has 1-Feb-2008, F1 has 1-March-2008, extend as far as necessary [format these dates as mmm-yy if you wish]

Then in D2 copied across

=MAX(0,MIN($B2,D$1+32-DAY(D$1+32))-MAX($A2,D$1)+1)