• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Formula to Spread Income or Costs Over Months

You are here: Home / Excel Formulas / Excel Formula to Spread Income or Costs Over Months
Excel Formula to Spread Income or Costs Over Months
August 5, 2014 by Mynda Treacy

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

The Challenge

We need to spread the cost/income over the months like I have done in the table below:

Spread Income or Costs example

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:

Calculate whether a date falls between 2 dates

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:

  1. Contract start date – 15/08/2014
  2. Contract end date – 29/05/2015
  3. 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!

Spread Income or Costs exampleDon’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:

calculate EOMONTH

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.

check if date falls within a date range

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:

calculate days within a range

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.

Excel Formula to Spread Income or Costs Over Months

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:Sorting Excel Date SlicersSorting Excel Date Slicers
Next Post:Excel Slicers – Everything You Need to KnowExcel Slicers

Reader Interactions

Comments

  1. Greg

    November 16, 2020 at 5:34 am

    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?

    Reply
    • Mynda Treacy

      November 16, 2020 at 9:22 am

      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

      Reply
      • Greg

        November 20, 2020 at 8:55 pm

        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

        Reply
  2. Dommy

    August 14, 2020 at 4:50 pm

    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!

    Reply
    • Dommy

      August 14, 2020 at 4:52 pm

      Or a different process that achieves the same results?

      Thanks

      Reply
      • Mynda Treacy

        August 14, 2020 at 5:53 pm

        Hi Dommy, Please post your Excel question and sample file on our forum where we can help you further. Mynda

        Reply
  3. Hiral

    June 30, 2020 at 12:50 am

    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!

    Reply
    • Mynda Treacy

      July 2, 2020 at 8:01 pm

      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

      Reply
  4. Rangaswamy

    June 29, 2020 at 7:45 pm

    Mind blowing…I wasted 2 days in trying different and long formulas. You made my day!!!

    Thank you very much

    Reply
    • Mynda Treacy

      June 29, 2020 at 7:52 pm

      Pleased to hear it was helpful!

      Reply
  5. Vinay

    June 22, 2020 at 4:53 pm

    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

    Reply
    • Mynda Treacy

      June 23, 2020 at 8:55 am

      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

      Reply
      • Michael

        February 24, 2021 at 9:20 am

        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

        Reply
        • Mynda Treacy

          February 24, 2021 at 1:22 pm

          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
           

          Reply
  6. OA

    February 19, 2020 at 8:54 pm

    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

    Reply
    • Catalin Bombea

      February 20, 2020 at 2:08 am

      Try this one:
      =FLOOR((TODAY()-StartDate)/365,5)
      Replace StartDate with a reference to your start date cell.

      Reply
  7. Alem tadesse

    December 21, 2019 at 3:36 pm

    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 .

    Reply
    • Mynda Treacy

      December 22, 2019 at 10:22 am

      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

      Reply
  8. Dee

    October 4, 2019 at 5:00 am

    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.

    Reply
    • Mynda Treacy

      October 5, 2019 at 7:44 pm

      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

      Reply
  9. MR S

    September 26, 2019 at 9:24 pm

    Nothing short of genius. Thank you!!!

    Reply
    • Mynda Treacy

      September 26, 2019 at 9:35 pm

      Glad it was useful 🙂

      Reply
  10. Peter

    August 24, 2019 at 3:56 am

    I love this formula. Can it be modified to calculate only working days in a month?

    Reply
    • Mynda Treacy

      August 24, 2019 at 7:12 pm

      Thanks, Peter. Yes, you can use the WORKDAY function to return working days.

      Mynda

      Reply
  11. Ben Borton

    March 16, 2019 at 6:41 am

    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.

    Reply
    • Catalin Bombea

      March 19, 2019 at 1:59 pm

      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

      Reply
      • Trevor

        September 19, 2019 at 12:15 am

        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

        Reply
        • Catalin Bombea

          September 19, 2019 at 1:45 am

          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

          Reply
  12. matthew redekopp

    November 2, 2018 at 9:06 am

    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?

    Reply
    • Mynda Treacy

      November 2, 2018 at 10:05 am

      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

      Reply
  13. Jo

    February 6, 2018 at 5:02 pm

    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.

    Reply
  14. Jessi

    April 19, 2017 at 5:17 am

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

    Reply
    • Catalin Bombea

      April 20, 2017 at 4:19 pm

      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

      Reply
  15. Chris Lomax

    November 25, 2016 at 11:40 pm

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

    Reply
    • Mynda Treacy

      November 27, 2016 at 8:21 pm

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

      Mynda

      Reply
  16. RJ

    May 18, 2016 at 6:31 am

    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.

    Reply
    • Catalin Bombea

      May 18, 2016 at 3:50 pm

      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

      Reply
      • MJ

        June 3, 2019 at 11:35 am

        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?

        Reply
        • Catalin Bombea

          June 4, 2019 at 1:20 pm

          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

          Reply
          • Madi J

            June 6, 2019 at 10:30 am

            Uploaded ! 🙂

  17. René

    July 14, 2015 at 10:15 pm

    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é

    Reply
    • Mynda Treacy

      July 14, 2015 at 10:57 pm

      Cheers, René 🙂 Glad you liked it.

      Reply
  18. Mohammed

    August 29, 2014 at 5:18 pm

    Cool. Thanks!!

    Reply
    • Mynda Treacy

      August 29, 2014 at 7:13 pm

      You’re welcome, Mohammed 🙂

      Reply
  19. Col Delane

    August 12, 2014 at 3:11 pm

    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

    Reply
    • Mynda Treacy

      August 12, 2014 at 4:02 pm

      Thanks for sharing, Col.

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

      Mynda

      Reply
      • Col Delane

        August 12, 2014 at 5:37 pm

        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

        Reply
        • Mynda Treacy

          August 12, 2014 at 7:55 pm

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

          Reply
  20. Don

    August 7, 2014 at 12:10 pm

    Modifying Richard’s formula:
    =MAX(0,MIN(EOMONTH($A9,0),$B$5)-MAX(EOMONTH($A9,-1),$B$4))*$B$6

    Reply
    • Mynda Treacy

      August 7, 2014 at 12:25 pm

      Love it, Don.

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

      Reply
  21. Dave Bonin

    August 7, 2014 at 1:06 am

    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.

    Reply
    • Mynda Treacy

      August 7, 2014 at 7:27 am

      Cheers, Dave.

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

      Mynda

      Reply
  22. Dan

    August 6, 2014 at 9:50 pm

    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.

    😉

    Reply
    • Mynda Treacy

      August 6, 2014 at 9:57 pm

      Cheers, Dan.

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

      Mynda

      Reply
  23. Richard

    August 6, 2014 at 9:33 pm

    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

    Reply
    • Mynda Treacy

      August 6, 2014 at 9:54 pm

      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

      Reply
  24. Jef

    August 6, 2014 at 3:36 pm

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

    Reply
    • Mynda Treacy

      August 6, 2014 at 9:05 pm

      Cheers, Jef 🙂

      Reply
  25. Kris

    August 5, 2014 at 6:34 pm

    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

    Reply
    • Mynda Treacy

      August 5, 2014 at 7:25 pm

      Wonderful!

      Thanks for sharing, Kris 🙂

      Reply
    • roberto mensa

      August 14, 2014 at 10:40 pm

      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 🙂

      Reply
      • roberto mensa

        August 14, 2014 at 10:46 pm

        or with EOMONTH …
        =$B$3/($B$5-$B$4)*MAX(0,MIN(EOMONTH(A9,0),$B$5)-MAX(A9-1,$B$4))
        regards
        r

        Reply
      • Mynda Treacy

        August 14, 2014 at 10:48 pm

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

        Thanks to Daniel too.

        Mynda

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.