How to find Number of Months

[Priyantha]

Dear All,

I am trying to find, number of specified month (Eg. "April" months), between two days using VBA. No any aia about this pleas help me.
Eg.
Cell A1, Start Date _ 2020/02/13, Cell B1, End Date _ 2024/01/21, I want to get result Cell C1, number of April months. (Result should 4).

BR,

Priyantha.

[HansV]

With start date in A1, end date in B1, and month number (4 for April) in C1, you could use the formula

=LET(d,SEQUENCE(B1-A1+1,,A1),y,FILTER(YEAR(d),MONTH(d)=C1),COUNT(UNIQUE(y)))

See next reply for VBA code.

[HansV]

Create the following custom function:

Code: Select all

Function CountMonths(StartDate As Date, EndDate As Date, MonthNum As Long) As Long
    Dim d As Date
    d = StartDate - Day(StartDate) + 1
    Do While d <= EndDate
        If Month(d) = MonthNum Then
            CountMonths = CountMonths + 1
        End If
        d = DateAdd("m", 1, d)
    Loop
End Function

Use like this:

Code: Select all

Sub Test()
    MsgBox CountMonths([A1], [B1], [C1])
End Sub

[Priyantha]

I tried above code,

=LET(d,SEQUENCE(B1-A1+1,,A1),y,FILTER(YEAR(d),MONTH(d)=C1),COUNT(UNIQUE(y)))

Not working in excel, massege was displayed "This function isn't valied" with highlighting the word "FILTER"

while running VBA code result were displayed as "0"

BR

Priyantha

[SpeakEasy]

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"mm/dd")="04/01"))

[Priyantha]

it is working . can you pleas explain, using in vba.



BR,

Priyantha

[HansV]

The Excel function only works in Microsoft 365 and Office 2021.

The VBA function works for me...

S2511.png

[Priyantha]

My Excel version is "Microsoft Office Professional Plus 2016".

This code not working "=LET(d,SEQUENCE(B1-A1+1,,A1),y,FILTER(YEAR(d),MONTH(d)=C1),COUNT(UNIQUE(y)))" and VBA code not working. What is my issue. (I attached my Excel file).


Br,

Priyantha

[HansV]

As I mentioned, the formula that I posted only works in recent versions of Excel. It won't work in Excel 2016.

The VBA code requires that you specify the month number instead of hard-coding it. See the attached version.

Manths.xlsm

[SpeakEasy]

My Excel function above should work in earlier versions of Excel, but basically works by counting the number of Apr 1sts between the two dates, so has some edge conditions where it gets the wrong result (Hans Excel function and VBA function suffer similarly). For example, try a start date of 1 Apr 2023 and a finish date of 1 Apr 2024

Mine can be slightly tightened up as

=QUOTIENT(SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&B2)),"mm")="04")),30)

(and I am sure Hans can do something to both his Excel formula and his VBA as well)

[Priyantha]

Thank Both of you for helping me

Br,

Priyantha

[Priyantha]

I am tring to a next step. I hope to add or delete number of rows acording to Number of December months in my period. (Eg. period 2021-09-18 to 2023-12-10, No. 03 December months).

As my Example, A one row should be remain as defalt between A42 ("Year") & A47 ("Sum"), although there was no Decembeber months in my range. (Coloured in Green). while change the period, in A11 to A38 (My Period), shoould add or remove rows according to No. of December to green colour area.

AlthoughI ttied a small code & do many changes, always one row may be add more or not deleted and it is better if the effect od adding or deleting rows in between colum A and I, like "Shift cell up" methord while removing rows. any aothr methord while adding rows.

I am waiting for your help.

Br,

Priyantha.

[snb]

Aprils:

Code: Select all

=YEAR(B1)-YEAR(A1)-(MONTH(A1)>4)-(MONTH(B1)<4)+1

Code: Select all

Sub M_snb()
   c00= dateserial(2020,2,4)
   c01= dateserial(2024,8,14)

   msgbox year(c01)-year(c00)+(month(c00)>4)+(month(c01)>4)+1
end sub

[Priyantha]

Thanks for helping,

Have Any answers to my above issue.

[SpeakEasy]

Aprils:

Code: Select all

=YEAR(B1)-YEAR(A1)-(MONTH(A1)>4)-(MONTH(B1)<4)+1

Try feeding it 30 Apr 2023 through to 1 Apr 2024 ...

(similar edge condition problem as my original and HansV's)

[snb]

What should be the result in your opinion ?

[Priyantha]

Thanks, i get correct answer (4) for that on you and Hans guidance.

My next issu mention above. Are there any ideas about that?

[SpeakEasy]

>What should be the result in your opinion ?

It's the OPs opinion we really need. However, in my opinion , as there is no complete month of April between those two dates, the answer should be 0

A ,possibly trickier situation is if there are 30 (or multiples thereof) April days between the dates, but those days are split between the start year and the finish year, eg 14 April 2023 (17 April days in 2023) to 13 April 2024 (13 April days in 2024). Does a split month count as a month or not? If it does, then the answer in this case should be one, if not then 0. But not 2.

[Priyantha]

For SpeakEasy, I got the answer using belo code presented by HansV and thaks your support.

Function CountMonths(StartDate As Date, EndDate As Date, MonthNum As Long) As Long
Dim d As Date
d = StartDate - Day(StartDate) + 1
Do While d <= EndDate
If Month(d) = MonthNum Then
CountMonths = CountMonths + 1
End If
d = DateAdd("m", 1, d)
Loop
End Function


Sub Test()
MsgBox CountMonths([A1], [B1], [C1])
End Sub

[Priyantha]

I am tring to a next step. I hope to add or delete number of rows acording to Number of December months in my period. (Eg. period 2021-09-18 to 2023-12-10, No. 03 December months).

As my Example, A one row should be remain as defalt between A42 ("Year") & A47 ("Sum"), although there was no Decembeber months in my range. (Coloured in Green). while change the period, in A11 to A38 (My Period), shoould add or remove rows according to No. of December to green colour area.

AlthoughI ttied a small code & do many changes, always one row may be add more or not deleted and it is better if the effect od adding or deleting rows in between colum A and I, like "Shift cell up" methord while removing rows. any aothr methord while adding rows.

I am waiting for your help.

Br,

Priyantha.

SpeakEasy

Have you ani aidias about this