Forum Discussion

Hervinder's avatar
Hervinder
Copper Contributor
Dec 09, 2018

Split cost between between two dates

I have a spreadsheet where i'm putting a start date and end date in two columns, in the next column i have a total cost and then the months in the columns after

 

What i am trying to achieve is if a user enters a start date for example 10-Oct and end date being 12-Dec, i would like the cost to be split as follows

 

20 days in Oct

30 days in Nov

12 days in Dec

 

i have managed to work out the number of days between the two dates being 63 days, Now i want to put a formula in each month so if the dates fall into that month the cost for each day in that month is calculated.

 

I've attached an example spreadsheet

 

I tried so many different ways and struggled

 

Thanks in advance

BI & Data Analysis
excel
Formulas and Functions

3 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 10, 2018

    Hi,

     

    To do that, you need this thing:

    =IF(AND(MONTH(F6)>=MONTH($A$7),MONTH(F6)<=MONTH($B$7)),
    IF(MONTH(F6)=MONTH($A$7),DAY(EOMONTH($A$7,0))-DAY($A$7)+1,
    IF(MONTH(F6)=MONTH($B$7),DAY(DAY($B$7)),DAY(EOMONTH(F6,0)))),0)

    But please note that the remaining days in Oct is 22, not 20.

     

    To make the solution easier, I've changed the month headers to the first date of each month but I changed the format to show only the month.

     

     

    Hope that helps

    • vaibhavilnt's avatar
      vaibhavilnt
      Copper Contributor
      Oct 17, 2021
      This formula does not work when my start date it and end date are not in the same calendar year. for example my start date is 14 October 2021 and end date is is 4th July 2022.
      Generally I use MSP for doing this task but currently to work in Excel. Is there a way?
    • Mta5335's avatar
      Mta5335
      Copper Contributor
      Jan 08, 2021
      Absolutely amazing, would have taken me ages otherwise! thank you

Resources