*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.*

## The Challenge

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:

=$A9=MEDIAN(EOMONTH($B$4,-1)+1,EOMONTH($B$5,0),$A9)

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:

=MEDIAN(15/08/2014,29/05/2015,01/09/2014)

And therefore the result is:

`=01/09/2014`

So when we then compare the result returned by MEDIAN to the date in cell A11 (for September,) it looks like this:

=01/09/2014=01/09/2014

In English it reads:

=Does the date in cell A11=The Date Returned by MEDIAN

And the result is:

=TRUE

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.

### EOMONTH

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 1^{st} 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.

=MEDIAN(15/08/2014,29/05/2015,01/08/2014)

Evaluates to:

`=15/08/2014`

And then our logical test would be:

Does the month date in cell A10= MEDIAN(15/08/2014,29/05/2015,01/08/2014)

=01/08/2014=15/08/2014

And the answer is:

=FALSE

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:

=$A9=MEDIAN(EOMONTH($B$4,-1)+1,EOMONTH($B$5,0),$A9)

The EOMONTH function finds the last day of the month before or after a specified number of months. The syntax is:

=EOMONTH(start_date, *months*)

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:

`=EOMONTH($B$4,-1)+1`

Which evaluates to:

`=EOMONTH(15/08/2014,-1)+1`

The -1 for the *months* argument finds the last day of the month prior:

`=31/07/2014+1`

Then we add 1 to take it to the first day of the following month:

`=01/08/2014`

Likewise for the last day of the contract:

`=EOMONTH($B$5,0)`

Which evaluates to:

`=EOMONTH(29/05/2015,0)`

Remember a zero takes you to the end of the month, so it then becomes:

`=31/05/2015`

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:

=MIN($B$5,EOMONTH($A10,0))-MAX($B$4,$A10-1)

Let’s look at the MIN part for our month end calculation first:

=MIN($B$5,EOMONTH($A10,0))

In English it reads, find the minimum of the contract end date and the end of the month in cell A10 i.e.

=MIN(29/05/2015,31/08/2014)

Which evaluates to:

`=31/08/2014`

And our month start calculation is:

=MAX($B$4,$A10-1)

Which is:

=MAX(15/08/2014,1/08/2014-1)

Which becomes:

=MAX(15/08/2014,31/07/2014)

To finally evaluate to:

`= 15/08/2014`

And the two together are:

=MIN($B$5,EOMONTH($A10,0))-MAX($B$4,$A10-1)

Which evaluates to:

=MIN(29/05/2015,31/08/2014)-MAX(15/08/2014,31/07/2014)

And then:

=31/08/2014-15/08/2014

=16 days

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

`=$B$3/($B$5-$B$4)`

2. Logical Test to see if month falls within contract period

`=$A10=MEDIAN(EOMONTH($B$4,-1)+1,EOMONTH($B$5,0),$A10)`

3. Number of days in contract period for the month

`=MIN($B$5,EOMONTH($A10,0))-MAX($B$4,$A10-1)`

And altogether now:

=$B$3/($B$5-$B$4)*($A10=MEDIAN(EOMONTH($B$4,-1)+1,EOMONTH($B$5,0),$A10))*(MIN($B$5,EOMONTH($A10,0))-MAX($B$4,$A10-1))

Which evaluates to:

=1500*TRUE*16

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:

=1500*1*16

Which is:

=24,000

You probably know where I’m going next. Yep, those negative days. Let’s look at the formula for July 2014:

=$B$3/($B$5-$B$4)*($A9=MEDIAN(EOMONTH($B$4,-1)+1,EOMONTH($B$5,0),$A9))*(MIN($B$5,EOMONTH($A9,0))-MAX($B$4,$A9-1))

=1500*FALSE*-15

Remember FALSE converts to a zero so our formula looks like this:

=1500*0*-15

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? 😉

## Thanks

I learnt that MEDIAN trick from my friend, Roberto Mensa.

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.

## Please Share

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.

Jessi says

Is it possible to do this formula using fiscal quarters instead of months?

Catalin Bombea says

Hi Jessi,

It can be done, but fiscal years are not the same in each country.

Can you please upload a sample file with your layout on our forum? (create a new topic). A manual example of the desired result will speed up the process, please take your time to write a good description of the problem on the forum.

Thanks

Catalin

Chris Lomax says

Hi there. Would this also work for creating cost profiles with various costs over various time scales?

Mynda Treacy says

Possibly, Chris. Can’t say for certain without seeing your examples. If you get stuck please post your question in our Excel Forum.

Mynda

RJ says

What if you want to do it by week instead of month? I want to spread the revenue over the weeks to see where we have holes by week instead of month.

Catalin Bombea says

Hi Rj,

Take a look at this version from our OneDrive folder.

The original formula is looking at the date from column A, and calculates the number of days left until the end of month (EOMONTH formula). The dates in column A are always the first day of the month. In the example file, I added new lines, with 8/09/2014, 15/09/2014 and 22/09/2014 dates, but the formula should calculate now the number of days between 2 consecutive dates instead of initial EOMONTH formula.

You will see the new formula in the file.

There are some problems: you will not be able to split the number of days in a month into 4 equal subsets, the last week will have more days than the rest (the formula will calculate only full days, not fractions of days).

Cheers,

Catalin

René says

Hi Mynda,

Clever use of MEDIAN. This opens up opportunities to use this function, as I now understand what it does. Great!

kind regards, René

Mynda Treacy says

Cheers, René 🙂 Glad you liked it.

Mohammed says

Cool. Thanks!!

Mynda Treacy says

You’re welcome, Mohammed 🙂

Col Delane says

Hi again Mynda

I finally got around to looking at this post – albeit whilst eating lunch when I do a lot of Excel reading & research.

Also being a bean counter from way back, such that month-end dates are now ingrained in my soul (:-(, I too went in the same direction as Richard. This also allows the use of the DAY function to easily return the number of days in a month.

I also spotted the issue that Dave identified – contracts of this type are usually active for both start and end dates, unlike financing/banking transactions (e.g. for interest calculation purposes) wherein the start date is counted but not the end (repayment) date.

My formula to calculate the days in each month (example for July, but same for all months) then became:

=MIN(MAX(0,A9-$B$4+1),DAY(A9),MAX(0,$B$5-EOMONTH(F9,-1)))

No IFs, or buts!!

Cheers from the West

Mynda Treacy says

Thanks for sharing, Col.

I think you meant to reference A9, not F9 in the last EOMONTH formula.

Mynda

Col Delane says

Ooops, sorry! I missed that reference when editing my formula to match your table – mine was built in your spreadsheet but to the right of your table.

Thanks

Mynda Treacy says

No worries, Col. I figured that was the case 🙂

Don says

Modifying Richard’s formula:

=MAX(0,MIN(EOMONTH($A9,0),$B$5)-MAX(EOMONTH($A9,-1),$B$4))*$B$6

Mynda Treacy says

Love it, Don.

Perfect for all those set in their ways of entering month dates as the 1st 🙂

Dave Bonin says

It appears there may be a small issue with the number of days.

The formulas presented calculate the number of days between August 15, 2014 and May 29, 2015. They do not count both August 15 and May 29. Always consider whether the beginning and ending dates are inside or outside of the reporting period and add or subtract one day as necessary.

Mynda Treacy says

Cheers, Dave.

I forgot to mention considering the ‘actual’ start and end dates of the contract. Thanks for clarifying that point.

Mynda

Dan says

Mynda

I thought I was the only one who thought like this…..Absolutely wonderful, I followed right along knowing what your were doing.

Oh! it has been a long since I’ve heard it but I loved your “absolutely positootly, yes!” comment it also reminded me of the Three Stooges Curley’s “Soitenly!” saying!

Finally, try Excel “Wizzard”…. for a title! I’ve picked up that one over the years from co-workers. At one time I even had the Mickey Mouse Wizzard hat on display so new folks could figure out who they were talking about.

You know your life is to happy…..really now…….get serious. If you do I’ll quit reading your columns.

😉

Mynda Treacy says

Cheers, Dan.

Great to hear you enjoyed the read. I ‘soitenly’ enjoyed writing it 😉

Mynda

Richard says

Hi Mynda

Thanks for the post.

The way I have tackled these types of calculations in the past has been by formatting the dates so the days can be seen (dd-mmm-yyyy), which means you can dispense with the eomonth parts of the formula and work on an actual basis which would allow for intra month calculations. You also avoid the US date format issue.

Then A8 would be your starting date or the end of the preceeding month and if full month calculations are required and A9 could be as follows:

=eomonth($A8,1) which can then be dragged down as far as needed

or A9 is any date for which the amount is required.

The formula can then be as simple as:

=MAX(0,MIN($A9,$B$5)-MAX($A8,$B$4))*$B$6

Cheers

Richard

Mynda Treacy says

Hi Richard,

Awesome formula, thanks for sharing.

Now if only we can get everyone to enter their dates as the last day of the month instead of the first we can simplify many formulas. A great lesson, although a habit hard to break 🙂

One thing you mention that I’d like to clarify for others:

“formatting the dates so the days can be seen (dd-mmm-yyyy), which means you can dispense with the eomonth parts of the formula and work on an actual basis which would allow for intra month calculations”

Formatting the dates so the days can be seen does not simply allow you to dispense with the EOMONTH formula, the key to getting rid of EOMONTH is entering the date as the

end of the month, instead of the first. EOMONTH is then redundant.You can then format the date any way you want, including ‘mmm-yy’ and it doesn’t change the result of your formula.

Kind regards,

Mynda

Jef says

This is a pretty good post. Works well. Thanks for sharing.

Mynda Treacy says

Cheers, Jef 🙂

Kris says

Hi Mynda,

As I see the sign of this part:

=MIN($B$5,EOMONTH($A9,0))-MAX($B$4,$A9-1)

also tells if the month is within the contract period or not, so I maybe the formula is simpler this way:

=$B$3/($B$5-$B$4) * (MIN($B$5,EOMONTH($A9,0))>MAX($B$4,$A9-1)) * (MIN($B$5,EOMONTH($A9,0))-MAX($B$4,$A9-1))

Cheers,

Kris

Mynda Treacy says

Wonderful!

Thanks for sharing, Kris 🙂

roberto mensa says

Can I shorten?

=$B$3/($B$5-$B$4)*MAX(0,MIN(DATE(YEAR(A9),MONTH(A9)+1,0),$B$5)-MAX(A9-1,$B$4))

🙂

p.s.

Mynda I learnt that MEDIAN trick from our friend Daniel Ferry 🙂

roberto mensa says

or with EOMONTH …

=$B$3/($B$5-$B$4)*MAX(0,MIN(EOMONTH(A9,0),$B$5)-MAX(A9-1,$B$4))

regards

r

Mynda Treacy says

🙂 of course you can! Thank you for sharing, Roberto.

Thanks to Daniel too.

Mynda