Basically, here is what we are trying to do:
Check to see if BOTH of the following conditions are true:
- The "Start" date (column B) is before (or on) the last day of the quarter we are looking at
- The "Finish" date (column C) is after (or on) the first day of the quarter we are looking at
So, we need to build these quarter start/end dates. We do that by using the the DATE function, which has the format:
=DATE(year, month, day)
The key to getting this to working for every cell without having to adjust the formula is to make use of two important things:
- The absolute reference sign (
$). By placing this in front of column or row references, it "locks" down that part of the range reference, so it won't float when we move to other cells (see here for a good explanation of that:
http://www.cpearson.com/excel/relative.aspx)
- The
COLUMN() function, which returns the column number of whatever cell the formula is placed in. To see this, place this formula in cell A1 and copy across the row and see the value change.
The other two keys are the
INT and
MOD functions, which we use with division.
The
INT function returns just the integer portion of a number (meaning, it drops any decimals)
The
MOD function returns the remainder when dividing one number by another.
(You can Google these functions to see more details and examples of how they work, if you like).
So, now just piece it all together. If you want to see what the year portion returns, simply extract that part of the formula and place is in any blank row in column D, i.e.
If you substitute each part, you will have:
=2018+INT(4/4)-1 = 2018+INT(1)-1 = 2018+1-1 = 2018
Because we are dividing by 4, the INT portion will jump up by one every 4th column (you can drag this formula out to column S to see how it changes).
You can repeat the same type of logic with the month (I will leave that part to you).
One other trick that I enlist, since months have different number of days, to get the last day of a particular month, you can choose day 0 of the following month, i.e.
To represent 3/31/2018, you could use:
=DATE(2018,3,31)
or
=DATE(2018,4,0)
The second is easier to work with, as you do not need to worry about with months have 30 days and which have 31.
