WARNING: this tutorial contains some advanced formula concepts which may incur the following side effects:
Negative side effects:
Headache, urge to bang head on desk, fidgeting in seat, urge to look for distractions like e-mail to give your brain a rest.
Positive side effects:
Jumping up and cheering out loud when you realise you cracked it (which may result in people pointing and staring at you), desk-slapping joy, being known as the ‘office geek’ or ‘Excel Guru’.
Proceed at your own risk, and when you do succeed wear those labels with pride.
Ok, let’s say you have a contract which spans several months or years, with evenly spread cash flows.
Here are the details:
Contract Price: $430,500
Start Date: August 15, 2014
End Date: May 29, 2015
Note: the contract price could be income or expense since the logic is the same.
We need to spread the cost/income over the months like I have done in the table below:
Ahhh, this takes me back to my accounting days where we used to amortise the cost of assets and prepaid expenses. Sorry, I digress.
Ok, there are 3 steps to creating the table above and we’ll start out slowly:
Step 1: Calculate amount per day
The calculation for the amount per day is pretty straight forward. In English it reads:
= total contract price / (contract end date – contract start date)
=$430,500 / (29/5/2015 - 15/8/2014)
=$430,500 / 287 days
=$1,500 per day
Note: my dates are dd/mm/yyyy format.
Next we need to work out how much to allocate each month, and that’s not so straight forward! It wouldn’t be so tricky if we could just allocate an equal amount for each month, but our boss does things by the book and wants it based on the actual number of days in each month.
And here’s the stinger; notice how the contract Start and End dates are mid month. That right there adds another layer of complexity, but don’t worry I’ve got a clever way to handle that.
Step 2: Determine if the month falls within Contract dates
In cell B9 (see image below) we use a logical test formula which says:
=Are any of the dates in the month in cell A9 between the dates in the contract period?
The formula looks like this:
And evaluates to TRUE or FALSE as you can see in cells B9:B20 below:
Whoa there! Right now you might be thinking what has MEDIAN got to do with this?
Well, MEDIAN returns the number in the middle of a set of given numbers. The syntax is:
=MEDIAN(number1, [number2], [number3]…)
And when we give MEDIAN the following dates:
- Contract start date – 15/08/2014
- Contract end date – 29/05/2015
- A date in the month you’re checking – let’s say September 01, 2014 in cell A11.
If the month you’re checking is BETWEEN the contract start and end dates, then that date is the median. You can imagine that your MEDIAN part of the formula in cell B10 for September will look like this:
And therefore the result is:
So when we then compare the result returned by MEDIAN to the date in cell A11 (for September,) it looks like this:
In English it reads:
=Does the date in cell A11=The Date Returned by MEDIAN
And the result is:
In English TRUE reads: absolutely positootly, yes!
Don’t stand up and cheer yet. We’re not quite done. You may have noticed that I haven’t addressed the elephant in the MEDIAN formula; EOMONTH, so let’s look at that next.
Remember the ‘stinger’ I mentioned earlier where the contract dates are mid month?
Not only that, the ‘months’ in column A are actually just one day in the month, in this case the 1st of every month; if you look in the formula bar you can see the date in cell A9 for July 2014 is 1/07/2014:
Now this is only a problem for the first and last months in the contract period. Let’s look at August 2014 as an example.
And then our logical test would be:
Does the month date in cell A10= MEDIAN(15/08/2014,29/05/2015,01/08/2014)
And the answer is:
In English; FALSE reads: no way, José 😉
What we actually want it to say is TRUE, because some of August is in the contract period.
In order to get a TRUE returned from our logical test we need to change the contract start date to the first day in the month. And to avoid this same problem in May 2015 (i.e. when the contract ends), we also need to change the contract end date to the last day in the month.
For reference here is the formula again:
The EOMONTH function finds the last day of the month before or after a specified number of months. The syntax is:
The months argument can be:
- A positive value for months ahead
- A negative value for months prior
- A zero for the end of the current month
In our case we want to calculate the first day of the month the contract starts so our formula is:
Which evaluates to:
The -1 for the months argument finds the last day of the month prior:
Then we add 1 to take it to the first day of the following month:
Likewise for the last day of the contract:
Which evaluates to:
Remember a zero takes you to the end of the month, so it then becomes:
So now we know that the MEDIAN formulas in column B are actually testing to see if the date in column A falls between 01/08/2014 and 31/05/2015. We can see that August 2014 returns a TRUE, as does May 2015.
Aside: you might be thinking you could use the MONTH function to compare the month in column A to the Contract month dates, and you’d be right, but this will cause you problems when contracts span multiple years, which is why I’ve chosen this method.
Step 3: Calculate the number of days in the contract period for each month
In theory this calculation is quite straight forward:
=month end – month start
For example September will be:
=30/09/2014 – 01/09/2014
= 30 days
Except for the first and last months of the contract where we must use the contract dates as opposed to the start and end dates of the month. Stung again!
Therefore we need to get a bit clever and employ the aid of MIN and MAX to help Excel know when to use the contract dates vs the start/end dates of the month in column A. The formula we’ll use for August is:
Let’s look at the MIN part for our month end calculation first:
In English it reads, find the minimum of the contract end date and the end of the month in cell A10 i.e.
Which evaluates to:
And our month start calculation is:
To finally evaluate to:
And the two together are:
Which evaluates to:
If we look at column C below we can see the results of the MIN/MAX calculation for each month:
Uh oh….. July 2014 and June 2015 are negative days! Don’t panic, the final step which brings the 3 components together will eliminate the negative days quicker than Darth Vader can turn on his Light Sabre (or Light Saver as my kids say).
Bringing it All Together
Now we can bring our 3 components together (we’ll use August as our example):
1. Cost per day
2. Logical Test to see if month falls within contract period
3. Number of days in contract period for the month
And altogether now:
Which evaluates to:
When you apply a math operation to a Boolean TRUE or FALSE value they convert to their numeric equivalents, which are 1 and 0 respectively. So in actual fact our formula looks like this:
You probably know where I’m going next. Yep, those negative days. Let’s look at the formula for July 2014:
Remember FALSE converts to a zero so our formula looks like this:
And you know from school math that anything multiplied by a zero = zero! So we don’t have to worry about the negative day calculation because it gets eliminated by the logical test in step 2.
Now, you might be thinking that you could have written this formula with several nested IF’s and AND’s, and you’re right, but don’t you think this is more cleverer? 😉
Personally I think MEDIAN is the star in this formula. The way Roberto dresses MEDIAN in a BETWEEN Superhero cape is both elegant and unexpected.
Now it's your turn to take the baton. Think about ways you can use this idea in other Excel challenges you might have. Hint: It's not limited to dates.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.