View Full Version : 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
Paul_Hossler
06-02-2016, 06:13 AM
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:
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
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
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
06-03-2016, 06:06 AM
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
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
mikerickson
06-03-2016, 06:02 PM
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
06-04-2016, 12:23 AM
Thank you guys I wanted to count Sundays and this forum topic have helped me alot.
mdmackillop
06-04-2016, 02:32 AM
Hi SNB
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 ?
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.