Identify 3 pay-period Months

  • I have a spreadsheet that produces a monthly automated report for our HOA. By automated, I mean that it uses a combination of concatenated text and dollar figures to generate a series of annotation statements in the resulting report. The basis for adding one of the lines to the report is the number of bi-weekly pay periods (i.e. every other Wednesday) in a month, with the first one being January 13, 2021. Normally there are two per month, so nothing to report. But when there are three in a month (e.g. June and December for this year), a line is added to the report to explain that the “compensation overage is due to three pay periods for the month.”

    I need a sub to spit out the month names (or month numbers are okay, too) for the months that will have three pay periods, with the year as a variable. Since it’s a progressive 14-day increment, it cannot be based on a fixed date for each subsequent year.

    If what I’m asking is overly complicated, a simpler method that asks the user to input the starting date for the first pay period (e.g. 1/13 for this year, 1/12 for 2022, etc.) would be almost as good.

    Any help from someone who has tackled this, or can suggest how to configure what appears (given my limited Excel VBA knowledge) to be a rather complicated formula, would be greatly appreciated. Thanks for listening . . .

  • Without seeing how you plan to use this in an example workbook, one non-macro method to achieve something like this is attached.

    The Data sheet holds very basic information, just the year you want to work with and the month you want to query. The "has 3 pay periods" result for the month is then derived from the Calc sheet.

    The Calc sheet takes the Year value from the Data sheet, determines the 1st Wed in Jan to start and through a series of calcs derives the months (month number or month name) which have 3 pay periods.

    It may be possible to incorporate something like this into your current workbook.

    Identify 3 pay-periods.xlsx

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!