Way to calculate number of days within months from dates in table

  • I have a table with "from" and "to" dates, plus a daily rate. I would like a formula that calculates the total charges arising by month.


    Is there a way to calculate a SUMPRODUCT based on the maximum/minimum of each individual value in the column within the table compared to the start/end date for the month? Just using MAX/MIN within an array of the SUMPRODUCT seems to calculate by reference to the whole column in comparison, which makes sense but isn't what I need.


    Attached is an example. On row 3 is the SUMPRODUCT that I need help with. In columns E to I there are individual workings for the two example rows to show the results I am looking for. In the intended implementation of this, however, there won't be space to do it that way. The final version will also be subject to a couple of extra criteria from additional columns in the main table.


    If there are any decent solutions to this out there I would be really grateful to know.

  • What are the extra criteria? It's best to have everything up front, otherwise any solution suggested may not work once you try adding the extra constraints.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • It will be an account reference, so only selecting rows that match the appropriate account or range of account numbers.


    The account reference will be added as a further column to the table, if that isn't obvious.

  • I've had a look at your file - I am really sorry, but I cannot work out what you are trying to do.


    It woudl be MUCH easier if you provided (manually) the results you are expecting to achieve.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • The manual version is in cells E6:I10. The number of days per month per line are in E6:I9 (ignore the fact that those day numbers are calculated). Row 10 multiplies those by the relevant day rates from the table, so in January should come to 31 days at £2, February is 28 days at £2 plus ten days at £1.


    The working in E3:I10 was the pie in the sky attempt to solve the problem, but if it worked would match the results in row 10. That version is based on the idea of substituting the values in the From and To columns if they fall outside of the month in question with the last day of the previous or current month. I don't think SUMPRODUCT can be made to work that way, but it has done a lot of other things I haven't thought possible and is my best candidate for solving this.


    I can make it work with
    =SUMPRODUCT((Table1[From]<E$1)*(Table1[To]>EOMONTH(E$1,-1))*(DAY(E$1)*Table1[Daily amount]))
    -SUMPRODUCT((Table1[From]<E$1)*(Table1[From]>EOMONTH(E$1,-1))*(Table1[From]-(E$1-DAY(E$1-1)))*Table1[Daily amount])
    -SUMPRODUCT((Table1[To]<E$1)*(Table1[To]>EOMONTH(E$1,-1))*(E$1-Table1[To])*Table1[Daily amount])


    That calculates a month of days for each row that has any days in the month, less days between the first and From date, less days from the To date to the end of the month. It isn't pretty and I wonder if anyone knows a neater way to do it.