Count number of mondays

Hello,

I want to count the number of e.g. mondays between two dates.
I want to do it in VBA.

Is such possible?

kind regards,
Willem - NL

Quote:

---

Originally Posted by wmr

Hello,

I want to count the number of e.g. mondays between two dates.
I want to do it in VBA.

Is such possible?

kind regards,
Willem - NL

---

Between -- Inclusive? i.e. Jun 6 to June 20 -- is that 3 or 1 Monday?

Including. I want to count the first and de last day aswell.

Maybe like:

Code:

---

Option Explicit
 
Sub test()
  MsgBox CountMondays(#6/6/2016#, #6/20/2016#)
End Sub
 
Function CountMondays(ByVal StartingDate As Date, ByVal EndingDate As Date) As Long
Dim d As Date
Dim n As Long
 
  For d = StartingDate To EndingDate
    If Weekday(d, vbMonday) = 1 Then n = n + 1
  Next
 
  CountMondays = n
 
End Function

---

Mark

Thanks Mark, great!

Cheers and glad we were able to help. :-)

And all others, of course :)

starting date: "01/15/2016" (mm/dd/yyyy)
number of days: 65

Code:

---

Sub M_snb()
    c00 = "01/15/2016"
    y = 65
    MsgBox Evaluate("sum(N(weekday(" & c00 & "+row(1:" & y & ")-1,2)=1))")
End Sub

---

starting date: "01/15/2016" (mm/dd/yyyy)
end date: "07/15/2016"

Code:

---

Sub M_snb()
  c00 = "01/15/2016"
  c01 = "07/15/2016"
  MsgBox Evaluate("sum(N(weekday(" & c00 & "+row(1:" & DateDiff("d", c00, c01) & ")-1,2)=1))")
End sub

---

or

Code:

---

Sub M_snb()
  c00 = CDbl(CDate("15-01-2016")) & ":" & CDbl(CDate("15-07-2016"))
  MsgBox Evaluate("sum(N(weekday(row(" & c00 & ")-1,2)=1))")
end sub

---

or

Code:

---

Sub M_snb()
    Names.Add "snb", Range(CDbl(CDate("15-01-2016")) & ":" & CDbl(CDate("15-07-2016")))
    MsgBox [sum(N(weekday(row(snb)-1,2)=1))]
End Sub

---

I was playing around with something like Mark's

Code:

---

Sub test()
    MsgBox CountMondays(#6/7/2016#, #6/10/2016#)
    MsgBox CountMondays(#6/6/2016#, #6/20/2016#)
    MsgBox CountMondays(#6/7/2016#, #6/20/2016#)
    MsgBox CountMondays(#6/6/2016#, #6/19/2016#)
    MsgBox CountMondays(#1/1/2016#, #12/31/2016#)
End Sub
 
Function CountMondays(ByVal StartingDate As Date, ByVal EndingDate As Date) As Long
    Dim d As Date, D1 As Date, D2 As Date
    Dim n As Long
   
    If Weekday(StartingDate) = vbMonday Then
        D1 = StartingDate
    Else
        D1 = StartingDate - Weekday(StartingDate) + vbMonday + 7
    End If
   
    If Weekday(EndingDate) = vbMonday Then
        D2 = EndingDate
    Else
        D2 = EndingDate - Weekday(EndingDate) + vbMonday - 7
    End If
       
    If D2 => D1 Then
        CountMondays = 1 + (CLng(D2) - CLng(D1)) \ 7
    Else
        CountMondays = 0
    End If
   
End Function

---

Code:

---

Function SamT_CountMondays(ByVal StartingDate As Date, ByVal EndingDate As Date) As Long

CountMondays = (EndingDate - StartingDate) / 7 + Abs(Weekday(StartingDate, vbMonday) = 1)
   
End Function

---

All the solutions work well, but I didn't realize there are some school holidays :devil2:

10/6/2016 till 10/16/2016
and
12/23/2016 tiil 1/8/2017

(and more of course)

I won't count those days.

I used the code of Mark for this workbook.

I made it a bit more flexible, so I can also count other days.

Date format is Dutch.

br
Willem

If A1 is the start date and B1 is the end date

FLOOR(A1,7)+IF(MOD(A1,7)>2,9,2) is the Monday after the start date (=A1 if A1 is a Mon)
FLOOR(B1,7)+IF(MOD(B1,7)<2,-5,2) is the Monday before the end date (=B1 is B1 is a Mon)

and
=MAX(0, ((FLOOR(B1,7)+IF(MOD(B1,7)<2,-5,2))-(FLOOR(A1,7)+IF(MOD(A1,7)>2,9,2)))/7+1)

is the number of Mondays (inclusive) between those two dates.

Thank you guys I wanted to count Sundays and this forum topic have helped me alot.

Hi SNB

Code:

---

Range(CDbl(CDate("15-01-2016")) & ":" & CDbl(CDate("15-07-2016")))

---

Ingenious.

@mdmackillop

Thank you. :content:

But be careful, you might trigger a discussion ;) ;)

Earlier in the timeline, I told that I forgot holidays.

In the file I have a couple of columns to make it clear.

I won't count the holidays.

Maybe you can help again.

kind regards,
Willem

Since you try to let us program things it would be better to use weeknumbers to indicate the holiday periods.
But as far as I know even my holidays contain mondays...

But in Holland we have days like second Easter Day, second Pentecost and Ascension Day (Always on Thursday). And also the following Fryday the school is closed.

Those days are not (always) part of a holiday periode.

Reference the wb in post #17, when you say Holidays, do you mean what some refer to as vacation or breaks (vs. an individual Holiday, such as Christmas or Easter)? I am gathering that based on the data, this is the case. If that is correct, are the dates inclusive of the start and end dates?

Reference post #19, I am not understanding. Do any of the listed Holidays occur on Monday?

As you might know: second Easter & second Pentacost are always mondays.

Toch is het eenvoudiger om de vakantieweken met een maandag aan te geven met het weeknummer.
Ben je bekend met helpmij.nl ?

Quote:

---

Originally Posted by snb

As you might know: second Easter & second Pentacost are always Mondays.

---

@snb: Presuming that was for me, thank you; I did not know that.

Mark