Calculate the number of weeks in a month 2

[jofarrell]

Anyone know if there is an easy way to calculate the number of weeks in a month. For example in June 1-2 was a Friday and Saturday making up week 1 then the 4 weeks after leaving 5 months for June. Basically same for July .. I would like to be able to call on week 1 instead of
June 1 - June 2, week 2 (June 3 - June 9) Less chance of user error that way.

Any ideas?

Joanne

 

[MichaelRed]

I'm somewhat confused (that is normal). Your explination leave me way to much room to wriggle. Are you contending that June and July of this year each have five (5) weeks? According to WHAT logic?

You note that June 1-2 are a "week" in June, and declare that July is " ... the same ... " and also has 5 'weeks', you July starts on a Sunday. How are these " ... the same ... "?

It is easy to "count" the number of occurances of a given day of the week within an interval - but your explination does not give me sufficient info to know what to "count", nuch less how to expresss it for your purposes.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jofarrell]

If you were filling out a time sheet where a week ran from Sunday to Saturday and every month had to have its own set of time sheets then for a week like July 29 - Aug 4 you would be filling out two time sheets. One for July 29 - 31 and one for August 1 - 4 (*grin* I dont make the rules I just have to somehow code them and that is what I mean by a "week" it would be the 5th week of July and 1st week of Augutus) So for any given month you may have 5 possible timesheets to fill in. I have to know how many in each month you have. That help any? And sorry for the confusion its sometimes easier to see than say.

Joanne

 

[jjames]

Private Function WeeksInMonth(dtDate As Date) As Integer
Dim iDays As Integer

iDays = Day(DateSerial(Year(dtDate), Month(dtDate) + 1, 0))

If (iDays Mod 7) > 0 Then
WeeksInMonth = iDays / 7 + 1
Else
WeeksInMonth = iDays / 7
End If

End Function

 

[jofarrell]

Thanks jjames that was exactly what I was thinking of

Joanne

 

[scorpio66]

Function nWeeks(tDate As Date)
    nWeeks = (DateDiff("d", tDate, DateAdd("m", 1, tDate)) + Weekday(tDate - Day(tDate) + 1) - 2) \ 7 + 1
End Function

Chaz

 

[scorpio66]

We have a differences in our results:

For example,

Feb 2001, I get 5, jjames gets 4

Calendar says:

M T W T F S S
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29

Which I reckon is 5. Actually, my code assumes that day 1 is Sunday, which also makes a difference (just not in this case ...)

Chaz

 

[jofarrell]

Ohh burst my bubble *grin* it worked for the ones I tried .. I will try Feb now myself and see what I get. Thanks for the heads up

Joanne

 

[scorpio66]

Actually, mine doesn't work either :-(

All months in 2001-2004 work, but I've just checked 2009 and it doesn't get May and August right.

Dodgy maths ...

Chaz

 

[jofarrell]

Well I guess I will just have to play around with the weeknumber and somehow manipulate it from there.

Thanks for all your trouble

Joanne

 

[scorpio66]

Ok, this one works a little better:

2001, 2002, 2003, 2004, 2009 all check OK.

Function nWeeks(tDate As Date)
    nWeeks = (DateDiff("d", tDate, DateAdd("m", 1, tDate)) + (Weekday(tDate - Day(tDate) + 1, vbSunday) - 1) - 1) \ 7 + 1
End Function

Chaz

 

[jofarrell]

That is a lot shorter than trying to figure out if the end of a month and the beginning of next fall in same week and then determining from one end depending on the number of days in that month how many timesheets they will have to fill out

Thank you for checking that I really appreciate it

Joanne

 

[scorpio66]

While this gives you what you asked for, it doesn't really take into account working days. You might need to modify it slightly for that (it may just involve changing vbSunday to vbMonday, but unfortunately I don't have a Monday based calendar to check against.)

Good luck

Chaz

 

[jofarrell]

I actually have a working days for month function, and with this type of work I am tracking it may involve weekends so a sunday based calendar is perfect

Thanks again for all your help.

Joanne

 

[MichaelRed]

So, if I may go back to the beginning, the real issue is to just know How many Saturdays actually are within the month (inclusive) AND wheather the LAST Day of the month is a Saturday?

I 'see' that the rest of you are 'in to' the Orwellian concept (less is more / brevity thing), I'm more into trying to keep it so I can rember what it was for (where llittle things like comments help), so I did my own. Presented strictly for the ability for 'old folks'.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jofarrell]

MichaelRed,

No my issue was to be able to determine if a date (for example July 31th) fell into the middle of a week where two time sheets would be needed. I have to display all time sheets submitted and totals so I wanted to be able to call on the data by month and total by week.

I am sorry if I confused you in anyway as it was not my intention. And I would be curious to see your "old folks method" if it differs from above.

Joanne

 

[MichaelRed]

I don't think the results differ. Just the "analysis". Since the code is provided, you - or others - may compare results.

In the arena of "analysis", the 'old folks" see that your reporting period (wheather time sheets, expense reports or other ilk of periodic doings) basically ends on Saturday. So, the "old folks" think ' ... we neeed to know how many saturdays are in the period ... ' as thee needs to be a 'reporting period' for every such event (Saturday). Then you add the minor addition / exception. If there is PART of a week within the period (the month), It also is counted as a reporting period. Since the counting of Saturdays will include any partial week periods at the start of the period, the 'old folks' think that we only need to know wheather the month ends on a saturday, because if it does, we have already counted it, otherwise there is some period between the last 'counted' Saturday and the end of the period which needs to included.

To actually "analyse" the problem, we could state:
[tab]All months have at least 4 Saturdays.
[tab]The number of days in a Month range from 28 to 31
[tab]For months with 29 days, If the Month Starts on Saturday, there is an extra Saturday.
[tab]For months with 30 days, there is an extra Saturday if the Month starts on or after Friday.
[tab]For months with 31 days, there is an extra Saturday if the Month starts on or after Thursday

Using these rules, 'old folks' could build a routine which never actually calculated the number of saturdays within the period (month), but just used the rules to determine the value. This process would still need to determinsethe "extra" which might be present at the end of the month, however this is always present unless the period (month) ends on the Saturday, so the logic calculation for this would be the same.

A seperate approach might be just to analyse the actual frequency of occurance of the numbers of periods for your process. It would reveal that the majority (~ 75%) of the months have 5 reporting periods. This approach would simply start with the 'assumption' of the 5 periods and adjust for the exceptions.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

O.K., so I'm just dumb enough to do it that way as well.

Public Function nRptPer(DateIn As Date) As Integer

    Dim nDays As Integer    'Number of days in the month
    Dim Mnth As Integer
    Dim Yr As Integer
    Dim StDt As Date
    Dim EndDt As Date
    Dim AddPer As Integer   'Number of periods to 'add'

    Mnth = Month(DateIn)                    'Just get the Month of interest
    Yr = Year(DateIn)                       'And the year

    StDt = DateSerial(Yr, Mnth, 1)          'Last date of previous Month
    EndDt = DateSerial(Yr, Mnth + 1, 0)     'Last date of Month of Interest

    nDays = DateDiff("d", StDt, EndDt) + 1
    'nDays = Day(EndDt)     'Alternative calc of number of days in Month.

    Select Case nDays
        Case Is = 28        'Only the 'Ususal' Fed.  ALWAYS -1
            AddPer = -1
        Case Is = 29        'Never needs an adjustment!

        Case Is = 30
            If (Weekday(StDt) = vbFriday) Then
                AddPer = 1
            End If

        Case Is = 31
            If (Weekday(StDt) > vbThursday) Then
                AddPer = 1
            End If
    
    End Select

    nRptPer = 5 + AddPer

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[scorpio66]

Michael,

While I agree with your sentiment (I am guilty of writing terse, uncommented code) I also think that you should write the code that you're asked for, not what you think the person wants.

Jo didn't ask for a function which counted the number of Saturdays in a month, she asked for a function which returned the number of distinct weeks which occurs in a month.

Your code doesn't do that.

Chaz

 

[scorpio66]

Joanne,

A simpler and more understandable version of the same code.

Function WeeksInMonth(tDate As Date)
    Dim sDate as Date  ' First of the month
    Dim eDate as Date  ' Last of the month

    sDate = DateSerial(Year(tDate), Month(tDate), 1)
    eDate = DateSerial(Year(tDate), Month(tDate) + 1, 0)
    WeeksInMonth = DateDiff("ww", sDate, eDate) + 1
End Function

Interesting problem

Chaz