Split cost between between two dates

%3CLINGO-SUB%20id%3D%22lingo-sub-298024%22%20slang%3D%22en-US%22%3ESplit%20cost%20between%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-298024%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20where%20i'm%20putting%20a%20start%20date%20and%20end%20date%20in%20two%20columns%2C%20in%20the%20next%20column%20i%20have%20a%20total%20cost%20and%20then%20the%20months%20in%20the%20columns%20after%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20i%20am%20trying%20to%20achieve%20is%20if%20a%20user%20enters%20a%20start%20date%20for%20example%2010-Oct%20and%20end%20date%20being%2012-Dec%2C%20i%20would%20like%20the%20cost%20to%20be%20split%20as%20follows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E20%20days%20in%20Oct%3C%2FP%3E%3CP%3E30%20days%20in%20Nov%3C%2FP%3E%3CP%3E12%20days%20in%20Dec%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20managed%20to%20work%20out%20the%20number%20of%20days%20between%20the%20two%20dates%20being%2063%20days%2C%20Now%20i%20want%20to%20put%20a%20formula%20in%20each%20month%20so%20if%20the%20dates%20fall%20into%20that%20month%20the%20cost%20for%20each%20day%20in%20that%20month%20is%20calculated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20an%20example%20spreadsheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20so%20many%20different%20ways%20and%20struggled%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-298024%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-298091%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cost%20between%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-298091%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20that%2C%20you%20need%20this%20thing%3A%3C%2FP%3E%3CPRE%3E%3DIF(AND(MONTH(F6)%26gt%3B%3DMONTH(%24A%247)%2CMONTH(F6)%26lt%3B%3DMONTH(%24B%247))%2C%3CBR%20%2F%3EIF(MONTH(F6)%3DMONTH(%24A%247)%2CDAY(EOMONTH(%24A%247%2C0))-DAY(%24A%247)%2B1%2C%3CBR%20%2F%3EIF(MONTH(F6)%3DMONTH(%24B%247)%2CDAY(DAY(%24B%247))%2CDAY(EOMONTH(F6%2C0))))%2C0)%20%3C%2FPRE%3E%3CP%3EBut%20please%20note%20that%20the%20remaining%20days%20in%20Oct%20is%2022%2C%20not%2020.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20make%20the%20solution%20easier%2C%20I've%20changed%20the%20month%20headers%20to%20the%20first%20date%20of%20each%20month%20but%20I%20changed%20the%20format%20to%20show%20only%20the%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Cost%20Split.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F62287i38B775E6D2A8A3A6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Cost%20Split.png%22%20alt%3D%22Cost%20Split.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2042811%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cost%20between%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2042811%22%20slang%3D%22en-US%22%3EAbsolutely%20amazing%2C%20would%20have%20taken%20me%20ages%20otherwise!%20thank%20you%3C%2FLINGO-BODY%3E
Occasional Visitor

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

2 Replies

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.

 

Cost Split.png

 

Hope that helps

Absolutely amazing, would have taken me ages otherwise! thank you