Week & Month calculations

[SDS100UK]

Hi, I wonder if there is a solution to this problem.

I have a payment frequency field which contains monthly or weekly. I then have a field with the figure paid on that frequency. What I need is to know how much is paid on a weekly basis, this is fine for the weekly payments but for the monthly simply multiplying the field by 4 is not sufficient due to 5 week months.


Any ideas??

TIA

Steven

 

[MichaelRed]

'Weekly' translates to every 7 days. There was a recent thread re how to figure out how any weeks were in a month. you could use any of the several soloutions shown in that thread to calculate how many 'weeks' are within a given calendar month. I'll post my (last) soloution here, but there are others which are equally valid.

Public Function WksinMnth_L(DateIn As Date) As Integer

    'Michael L. Red  7/5/2001

    Dim MnthDays(12) As Integer     'Array to hold the number of days in the month
    Dim DayFirst As Integer         'Weekday of the first of the month
    Dim AddPer As Integer           'Number of Periods / weeks to Adjust the standard (5)

    'Days in the Month
    MnthDays(1) = 31        'Jan
    MnthDays(2) = Day(DateSerial(Year(DateIn), 3, 0))
    MnthDays(3) = 31        'Mar
    MnthDays(4) = 30        'Apr
    MnthDays(5) = 31        'May
    MnthDays(6) = 30        'Jun
    MnthDays(7) = 31        'Jul
    MnthDays(8) = 31        'Aug
    MnthDays(9) = 30        'Sep
    MnthDays(10) = 31       'Oct
    MnthDays(11) = 30       'Nov
    MnthDays(12) = 31       'Dec

    DayFirst = Weekday(DateSerial(Year(DateIn), Month(DateIn), 1))

    Select Case MnthDays(Month(DateIn))
        Case Is = 28        'Only the 'Ususal' Fed.  ALWAYS -1
            If (DayFirst = vbSunday) Then
                AddPer = -1
            End If

        Case Is = 29        'Never needs an adjustment!

        Case Is = 30
            If (DayFirst = vbSaturday) Then
                AddPer = 1
            End If

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

    WksinMnth_L = 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

 

[SDS100UK]

Mike,

Thank you very much for that code.

Sorry to bother you again, but when I type it in and get a query to test it
it comes up with 5 for all records irrespective of date.

Any idea why this might be. I have checked and re-checked the code and got
a colleague to check it as well

Hope you can help


Kind regards


Steven

 

[rgbanse]

Steven,
Is this by chance a business schedule of 13 week quarters
where months don't actually end on the true EOM Date?
Month 1 = 4 weeks - April 29 thru May 26
Month 2 = 4 weeks - May 26 thru June 23
Month 3 - 5 weeks - June 24 thru July 28
RGB

 

[SDS100UK]

Hi RGB,

No it isnt.

I have a field that is payment freq. which is monthly or weekly. I need to have a total figure paid weekly, so I need to divide the monthly figure then add it to the weeklys'

Does this make any sense?

TIA

STeven

 

[MichaelRed]

Steven, the code I posted is for calculating hte number of weeks for periodic reporting such as time-sheets or expense reports. It counts a 'week' as any period ending on Saturday within the Month, and any period after the last Saturday before the end of the month. This is almost always the '5 weeks'. this routine - along with several others - has been tested through 2009 and have consistient results. The following "table" shows the 23 months between Jan, 01 and Dec, 09 where the number of weeks in the month are NOT equal to five. There are only 23 (of the 108), less than 25%.

I would NOT re-key the routine (or any "code" from the Tek-Tips), as you can easily do the standard Block / Copy / Paste as standard windows operations. I have no reason to expect the procedure would fail, but IF you are using non-U.S. settings for dates or language, these MAY have an impact I am unaware of. You could use the list of months in the table and run the procedure with one or more of them to check the results. If these are NOT as expected from the table, place a breakpoint on the last line ("End Function&quot and run it again. When it stops, look at the variables, record the values and forward the results to me and I will review what is awry.

MichaelRed
mred@att.net

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