How many mondays, tuesdays, etc in a month

[Hans810]

Dear all,

A new challenge! Can somebody help me with the vba code or formulas for extracting the number of mondays, tuesdays, etc from a specific month in a specific year? i need this information for calculating the amount of delivery days for our clients.

Thanks in advance.

Regards from freezing Holland!

 

[JamesW]

Hi,

Try this:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

Array Formula: Confirmed with CTRL, SHIFT + ENTER, not just ENTER.

Where,
A2 = Start Date
B2 = End Date
C2 = Day to count (1 = Sunday, 2 = Monday, 3 = Tuesday etc..)

Similar formulas here: http://www.cpearson.com/excel/DateTimeWS.htm

 

[MickG]

Run code and Insert "Month/Year" in Inputbox like:- 4/2012
Results in msgbox and starting "L1".

[COLOR="Navy"]Sub[/COLOR] MG09Jan42
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] dt [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] Nam [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c
c = 0
Txt = Application.InputBox(prompt:="Enter Month/Year, i.e :- 4/2012", Title:="Weekdays", Type:=2)
[COLOR="Navy"]If[/COLOR] Txt = "" Or Txt = "False" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    dt = CDbl(DateValue("1/" & Txt))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]If[/COLOR] IsDate(dt) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]While[/COLOR] Val(Month(dt)) = Val(Split(Txt, "/")(0))
                dt = DateAdd("d", c, CDbl(DateValue("1/" & Txt)))
                Nam = WeekdayName(Weekday(dt), , vbSunday)
                c = c + 1
                [COLOR="Navy"]If[/COLOR] Not .Exists(Nam) [COLOR="Navy"]Then[/COLOR]
                    .Add Nam, 1: Temp = Nam
                [COLOR="Navy"]Else[/COLOR]
                    .Item(Nam) = .Item(Nam) + 1
                    Temp = Nam
                [COLOR="Navy"]End[/COLOR] If
            Wend
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] nTxT [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    [COLOR="Navy"]If[/COLOR] K = Temp [COLOR="Navy"]Then[/COLOR] .Item(K) = .Item(K) - 1
        nTxT = nTxT & K & " // " & .Item(K) & Chr(10)
[COLOR="Navy"]Next[/COLOR] K
Range("L1").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .Items))
MsgBox nTxT
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[barry houdini]

A2 = Start Date
B2 = End Date
C2 = Day to count (1 = Sunday, 2 = Monday, 3 = Tuesday etc..)

Given that setup this non-array formula will produce the same result for any period

=INT((WEEKDAY(A2-C2)+B2-A2)/7)

Specifically for a calendar month you can also use this formula where the end date isn't required

=4+(DAY(A2+34)< WEEKDAY(A2-C2))

Assumes A2 is 1st of month....

 

[Hans810]

Hi James,

Got it working!!
Thx a lot.

Br Hans