PDA

View Full Version : Count number of mondays



wmr
06-02-2016, 05:58 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

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?

wmr
06-02-2016, 06:35 AM
Including. I want to count the first and de last day aswell.

GTO
06-02-2016, 08:27 AM
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
06-02-2016, 08:57 AM
Thanks Mark, great!

GTO
06-03-2016, 02:35 AM
Cheers and glad we were able to help. :-)

wmr
06-03-2016, 02:49 AM
And all others, of course :)

snb
06-03-2016, 03:10 AM
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

SamT
06-03-2016, 06:25 AM
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
06-03-2016, 06:26 AM
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.

wmr
06-03-2016, 06:53 AM
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.

snb
06-04-2016, 04:11 AM
@mdmackillop

Thank you. :content:

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

wmr
06-07-2016, 12:47 AM
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
06-07-2016, 01:30 AM
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
06-07-2016, 04:37 AM
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
06-07-2016, 04:41 AM
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?

snb
06-07-2016, 05:07 AM
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 ?

GTO
06-07-2016, 05:18 AM
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