Number of weeks per month

[Pablo]

Hi all,

I need to track severance payments, which are paid every other Friday (bi-weekly). My schedule (reconciliation) is done on a monthly basis, so I track the monthly activity to the accounting system (ledger).

Now the problem I have is how do I estimate the right number of payments each month, normally is 2, but in March and August there are 3 (assume the 1st payment was done in Jan 4 2013, the 1st Friday of 2013). Also some severance starts or ends in the middle of the month, so the person only gets 1 payment instead of 2.

Probably it will be easier to understand my problem with the spreadsheet, but I don't see how to attach it.

Thanks,

Pablo

 

[Luke M]

Multi-step solution, but might work depending on your setup.


Let's say that you input the year of interest in A1. Formula to determine the first Friday of the year is:

=DATE(A1,1,CHOOSE(WEEKDAY(DATE(A1,1,1),2),5,4,3,2,1,0,6))


Assuming that formula is in B2, in b3 input:

=B2+14

and copy down till B28. You now have a list of all the Fridays that could be paid.


Next, to figure out how many in each month. We can generate a list of all the months with this formula, put in cell C2:

=DATE($A$1,ROW(A1),1)

Copy that down to C13


That gives us the list. To figure out sum for each month, in D2:

=SUMPRODUCT(1*(TEXT($B$2:$B$28,"mmyyyy")=TEXT(C2,"mmyyyy")))


Copy down to D13, and you're done. Now you can simply change the year in A1 to calculate all the numbers.

If you want to upload a workbook, check out the green stickey at the top of this forum.

 

[Pablo]

Thanks Luke, I will use your recommendations and let you know how it goes.

I never thought mixing SUMPRODUCT and TEXT.

 

[Luke M]

Hope it worked out. Using the TEXT function is handy since it lets you compare year and month at the same time, and the function won't throw an error if someone accidentally inputs some text or something.

 

[Pablo]

Hi Luke,

Thanks for your help. It did work, very nice solution.