Couting the Number of Weeks in a Month

I tasked myself with creating a personal budget spreadsheet in Excel. I know there are plenty of them out there, but I could not find one that calculates how many times a weekly or bi-weekly budget item occurs in a given month based on a particular start date. Like some of you I get paid every other week, which means that some months I get paid twice and others I get paid three times (woohoo!). As much fun as it is to figure out how many months I get three paychecks, I don't want to have to manually figure out the number of occurrences for each weekly and bi-weekly line item for each month. That's why computers were invented.

Not finding the exact formula out on the internet to give me what I was looking for (though some were helpful) and still being somewhat of a n00b in Excel, I realized I would have to figure it out using formulas I've never used before but assumed were out there. And I DID NOT want to resort to a VBA user-defined function (UDF); I have been following Chandoo's Excel blog - an unbelievably amazing place to "become awesome in Excel" - and therein have discovered the power of Excel without the need for VBA.

Specifications

• Return the number of weekly or bi-weekly occurrences in a given month
• Based on a Start Date
• Do not use VBA / UDF
• Do not use any other cells to hold portions of the calculation
• Do not use circular references
• Do not have an aneurysm trying to figure this one out

Not wanting to use additional cells to aid with the calculations, I knew the formula would have to involve arrays or something similar. Thankfully, I found out that Excel can do arrays. I also found out how to use the ROW and INDIRECT functions together to create the data to fill said arrays.

Variables

For this example, I copied this formula from cell AW7 of my spreadsheet. It gives me the number of occurrences of my budget line item for January (AW$4).

H is the column that contains the Budget Type:

• W = Weekly - every week based on the Start Date
• BW = Bi-Weekly - every other week based on the Start Date
• SM = Semi-Monthly - twice per month, ignoring the Start Date
• M=Monthly - once per month, ignoring the Start Date

I is the column that contains the Start Date

FiscalYear is the single cell named range that has holds the budget year

4 is the row that contains the month number, one column for each month in columns AW to BH. We will eventually be populating the same number of cells directly below AW4 - BH4 for each line item for which we are creating a budget.

Formula

Here is the formula I came up with:

{=IF(ISBLANK($H7),0,(IF($H7="M",1,(IF($H7="SM",2,SUMPRODUCT(IF(MONTH(ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0))))=AW$4,1,0)*IF(MOD(ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0)))-$I7,IF($H7="BW",14,7))=0,1,0)))))))}

Easy enough, right?

Breakdown

{

Right out of the gate you'll notice the curly braces: this denotes an Array Formula. This lets you do calculations on an array of information in memory without having actual cells for each value on your spreadsheet. When you type an Array Formula you need to press Ctrl-Shift-Enter (CSE) when you are done instead of just hitting Enter; you do not actually type the curly brace - Excel will do this by itself. More on how/why we use the Array Formula later.

=IF(ISBLANK($H7),0,

If our Budget Type cell is blank, return a 0 (zero) - we are not going on a budget for this line item

(IF($H7="M",1,

If our Budget Type is M, return a 1 (one), for once per month.

(IF($H7="SM",2,

If our Budget Type is SM, return a 2 (two), for twice per month.

SUMPRODUCT(

Here, we start our "else" section where the Budget Type is not blank, "M", or "SM"; this leaves "W" and "BW". SUMPRODUCT will give us a sum of all the rows of values in our arrays. We will use two arrays, and it will make more sense when you see what they are.

IF(

Starting with our first array, we are evaluating whether or not the month number returned for each value of the array is part of the month number we are specifying in AW$4.

MONTH(

Get the month number from the date serial number.

ROW(

We need to create an array of numbers to represent a date range. I saw the power of ROW(INDIRECT(...)) here and picked apart Ron Rosenfeld's "very nice" formula (towards the bottom) to see how it worked.

INDIRECT(

The INDIRECT function allows you to specify a range using a non-static value. For example, INDIRECT("1:2") gives us rows 1 and 2. INDIRECT(A1&":"&A2), where A1=5 and A2=10, gives us rows 5 through 10. If you specify a cell containing a date, the serial number for that date is used, and can be converted back to a date as needed.

$I7&":"&

This date range will begin with the Start Date ($I7)...

DATE(FiscalYear,AW$4+1,0)

...and will end on the last day of the specified month (AW$4). The DATE function is in the form DATE(year,month,day). If you notice, our day is 0 (zero); when you use a day of 0, it will give you the last day of the previous month. Hence, we add one (+1) to our month variable AW$4 to get the last day of that month. Don't worry, when we get to month 12, it knows to change month 12+1, day 0 to 12/31.

)))=AW$4,1,0)

We close our INDIRECT, ROW, and MONTH functions and ask if our month is the same as our selected month (AW$4). If it is, return a 1; otherwise, return 0.

*

Multiply our first array, row per row, with our second array. Our first array specifies if the date falls within the selected month (AW$4); our second will let us know if it also falls on a weekly or bi-weekly date, depending on the specified Budget Type ($H7), based on the Start Date ($I7). If both arrays return 1 (1*1=1) we know our budget line includes that date. Conversely, if either or both are 0 (1*0=0 and 0*0=0) our budget line does not include that date.

IF(

We begin our second array. As already mentioned we are finding out which dates fall every 7 or 14 days following the Start Date, depending on whether they are Weekly or Bi-Weekly, respectively.

MOD(

We can easily figure out our 7 or 14 day stepping by whether the remainder of dividing our dates by 7 or 14 is 0 (zero). The MOD function spits out the remainder of two numbers.

ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0)))

This should look familiar. It is exactly the same in the first array so I won't go into another explanation.

-$I7,

We subtract out the start date from each row in the array so we can do our MOD function. For example, if our date range in serial number format is 40,000 through 40,365, subtracting 40,000 gives us 0 through 365. Now we can divide by 7 or 14 and get a proper remainder.

IF($H7="BW",14,7)

Here we find out whether we are dividing by 7 or 14. If our Budget Type ($H7) is "BW", divide by 14; otherwise, divide by 7.

)=0,1,0)))

If our MOD is 0 (zero), meaning that it is evenly divisibly by 7 or 14, return 1; otherwise, return 0.

)

Close out our SUMPRODUCT function.

)))}

Close out our Budget Type IF statements and Array Formula. Don't forget: Excel adds the curly brace when you press CSE - you do not.

Where to go from here

There is plenty of room for improvement in this formula. For instance, the Start Date only works correctly if it is within January; adding a condition looking for a negative number would take care of that. An End Date would also be great. We could add "Bi-Monthly", "Annually" and "Semi-Annually" to the Budget Type list, and base all the Budget Types on the Start Date.

I hope this post not only gets you started with a formula ready to go but also gives you the knowledge to take it a few steps further. Let me know if you found it useful.