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.
Enter your email address below to download the sample workbook.
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 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.
=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.
Sorting Excel Date Slicers
Greg
Thanks for the formula, it works perfectly.
Is there a way of doing this so that full months are equally instead of the number of days in the month?
Mynda Treacy
Hi Greg, Yes, you can modify it to apportion the amounts equally over each month irrespective of the number of days. In step 1 you’ll need to calculate a monthly amount rather than a daily amount. Then in step 3 you’ll want to simply find if the month falls inside the contract period, rather than the number of days in the contract period. If you get stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further. Mynda
Greg
Hi Mynda,
Thanks for your reply. I tried to implement what you have advised above, but I am unable to get it to work.
I have put a post in the excel forum as you have advised.
Thanks
Greg
Dommy
Hi, love your work here. I’m wondering if there’s a way to carry out the exact same process, but for numerous contracts in an array?
Cheers!
Dommy
Or a different process that achieves the same results?
Thanks
Mynda Treacy
Hi Dommy, Please post your Excel question and sample file on our forum where we can help you further. Mynda
Hiral
How do I change and calculate the above formula if the working days for a contract is only mondays for between start and end dates and 3 hrs a day with hourly rate say 30 for example.
Thanks for help!
Mynda Treacy
Hi Hiral, please post your question on our forum where you can upload a sample Excel file with your data and desired result. Thanks, Mynda
Rangaswamy
Mind blowing…I wasted 2 days in trying different and long formulas. You made my day!!!
Thank you very much
Mynda Treacy
Pleased to hear it was helpful!
Vinay
Superb workings, it is helpful a lot.
Is there a formula instead of EOMonth to say EOyear?
I want to do something similar calculation for year bucket so where you use months, can you use the year e.g. 2013, 2014, 2015,2016 etc and share it with us? Thanks a lot.
Regards,
Vinay
Mynda Treacy
Hi Vinay,
You can replace the EOMONTH formulas with the following:
First day of year: DATE(YEAR($B$4),1,1)
Last day of year: DATE(YEAR($B$5),12,31)
Mynda
Michael
Hi Mynda,
This is very informative! If I wanted to show calculations for only the fiscal years in the contract (not details by month), how would I incorporate the formulas for first and last days mentioned above?
Assuming Contact Price is in cell A3 and amount is in B3:
Contact Price 430,500
Start Date. 08/15/14
End Date 05/29/15
Cost Per Day 1,500
2014
2015
How would the formula be written to get the amount of the contract price in FY 2014 (cell B8) and 2015 (cell B9)?
Thanks,
Michael
Mynda Treacy
Hi Michael,
Please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum
Thanks,
Mynda
OA
Hi,
I need to calculate our employees’ projected length of service for 5, 10, 15, 20 years etc (respectively) based on their respective start dates., and I will require a formula for this.
Is this something that you can assist with please?
Thanks
Catalin Bombea
Try this one:
=FLOOR((TODAY()-StartDate)/365,5)
Replace StartDate with a reference to your start date cell.
Alem tadesse
This was a cool tutor , i was using this but when the duration is one day meaning if it starts and finishes in the same day, it won’t show on the table. I need help .
Mynda Treacy
Hi Alem,
You could either adjust the start date by 1 day or put in an IF statement at the front of the formula to test if the start and end day are equal, and then enter the full amount on that day.
Mynda
Dee
hi there, thank you for this. What if your start and end date is the same, how do you account for that? Right now, utilizing this formula, it is giving me an error.
Mynda Treacy
Hi Dee,
If the start and end dates are the same then you don’t need this formula since the whole cost applies to just one date.
Mynda
MR S
Nothing short of genius. Thank you!!!
Mynda Treacy
Glad it was useful 🙂
Peter
I love this formula. Can it be modified to calculate only working days in a month?
Mynda Treacy
Thanks, Peter. Yes, you can use the WORKDAY function to return working days.
Mynda
Ben Borton
I used the above formula to make a projected earnings chart from a CPM schedule. I came up with 2 problems.
1) The formula does not work when the start and end dates are the same. I fixed this by manually changing the hours on either the start or end date so they were no longer identical.
2) If either a start or end date falls on the last day of a month, the formula does not work right. Is there a solution for this? To visualize this with the above example, change the end date to “31/05/2015 1:00:00 AM”.
Thanks.
Catalin Bombea
Hi Ben,
If the start date is the same as end date, there is no reason to spread the amount over months, it makes no sense, as the first month will have the entire amount, there is really no calculation needed here.
If either a start or end date falls on the last day of a month, the formula does work right, the time amount should not be there. Try with: “31/05/2015”, it will work.
Regards,
Catalin
Trevor
Hello,
Ben is correct in the fact that this does not work. I have an expense that should have a duration of 1 day, but the issue is that this formula recognizes the amount both in September and October which doubles my expense.
Is there a solution to this? I’ve tried to add IF statements, but can’t seem to get it to work.
Thanks,
Trevor
Catalin Bombea
Hi Trevor,
My guess is that you are not using the same structure described in this article and in the downloadable file.
For other data structures, please upload a sample file to our forum so we can see and understand your situation.
Thanks for understanding.
Catalin
matthew redekopp
How would you approach a dataset like that of utility bill records where the utility is metered between dates that straddle months? If you want to convert a table of records with straddled months into a table with readings for prorated months what would be the approach?
Mynda Treacy
Hi Matthew,
You could probably use Power Query to do this. If you post your question and sample Excel file on our forum we can help you further.
Mynda
Jo
Amazing formula – big thank you!
One comment, though: it will NOT work if the data is in a table. Took me 20 minutes to determine this was the cause. No biggie though, I’ll just stop formatting the data in a table.
Jessi
Is it possible to do this formula using fiscal quarters instead of months?
Catalin Bombea
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
Hi there. Would this also work for creating cost profiles with various costs over various time scales?
Mynda Treacy
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
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
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
MJ
Hi Catalin,
Thanks for this info! Super useful. I had, however, a small glitch while working with my file.
Th formula that you have posted for the ‘Weeks’ ends up including the entire months that the start and end periods are for, even if the dates were somewhere in the middle of the month.
How do we fix this?
Catalin Bombea
Hi MJ,
Can you upload a sample file with data on our forum? It will be easier to help based on your data structure.
Thank you
Madi J
Uploaded ! 🙂
René
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
Cheers, René 🙂 Glad you liked it.
Mohammed
Cool. Thanks!!
Mynda Treacy
You’re welcome, Mohammed 🙂
Col Delane
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
Thanks for sharing, Col.
I think you meant to reference A9, not F9 in the last EOMONTH formula.
Mynda
Col Delane
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
No worries, Col. I figured that was the case 🙂
Don
Modifying Richard’s formula:
=MAX(0,MIN(EOMONTH($A9,0),$B$5)-MAX(EOMONTH($A9,-1),$B$4))*$B$6
Mynda Treacy
Love it, Don.
Perfect for all those set in their ways of entering month dates as the 1st 🙂
Dave Bonin
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
Cheers, Dave.
I forgot to mention considering the ‘actual’ start and end dates of the contract. Thanks for clarifying that point.
Mynda
Dan
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
Cheers, Dan.
Great to hear you enjoyed the read. I ‘soitenly’ enjoyed writing it 😉
Mynda
Richard
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
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
This is a pretty good post. Works well. Thanks for sharing.
Mynda Treacy
Cheers, Jef 🙂
Kris
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
Wonderful!
Thanks for sharing, Kris 🙂
roberto mensa
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
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
🙂 of course you can! Thank you for sharing, Roberto.
Thanks to Daniel too.
Mynda