Count number of mondays

[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

[Paul_Hossler]

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?

[wmr]

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

[GTO]

Maybe like:

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

[wmr]

Thanks Mark, great!

[GTO]

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

[wmr]

And all others, of course

[snb]

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

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"

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

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

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

[Paul_Hossler]

I was playing around with something like Mark's

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

[SamT]

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

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

[wmr]

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

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.

[wmr]

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

[mikerickson]

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.

[Natasha]

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

[mdmackillop]

Hi SNB

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

Ingenious.

[snb]

@mdmackillop

Thank you.

But be careful, you might trigger a discussion

[wmr]

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

[snb]

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...

[wmr]

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.

[GTO]

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?