Weighting/ Flighting Data

[KingCal]

Good Morning,

I am hoping some of the brains on here can help with some a data query on Excel. I'm trying to flight costs across several Months/ Quarters correctly so i can view this accurately on one of my dashboards.

Eg. A sale that i make for £10,000 will have a duration from 01.03-30.04 - When viewing on my dashboard the full value gets attributed to March as this is the start date of the sale but i need it to Flight/Weight across the months/Quarters correctly allocating along the way. Example attached to hopefully help my request make sense.

Is it as simple as finding out how many days are between 2 dates, dividing the full total by those days and then manually allocating per sale. If so, thats alot of manual work as my business makes 1,000s of sales a year.

Any advise or ideas are most welcome. I'm fairly new to Excel but trying to make this dashboard work for my business

Thanks in Advance

 

[awoohaw]

I probably made this more complicated than it actually is. This is for months, and has helper rows. For quarters, you could add another helper row. Also, it will only work with end dates in the same calendar year as the start date. You have 365 so a Let and Lambda exercise would be more efficient. I have that will try to come up with something different.

WorkBook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Amount	Start	End
3		10000	3/15/2023	10/10/2023	3	4	5	6	7	8	9	10
4					16	30	31	30	31	31	30	10
5					765.55	1435.41	1483.25	1435.41	1483.25	1483.25	1435.41	478.47
Sheet1

Cell Formulas
Range	Formula
E3:L3	E3	=MONTH(EOMONTH(C3,SEQUENCE(,MONTH(D3)-MONTH(C3)+1,0,1)))
E4:L4	E4	=TEXTSPLIT(-DAY(C3)& IF(MONTH(D3)-MONTH(C3)>1, LEFT(",0,0,0,0,0,0,0,0,0,0,0",2*(MONTH(D3)-MONTH(C3)-1)), "")&IF(MONTH(D3)-MONTH(C3)>0,","&DAY(D3),""),",") + TEXTSPLIT(DAY(EOMONTH(C3,0))&IF(MONTH(D3)<>MONTH(C3), ","& IF(MONTH(D3)-MONTH(C3)<2,DAY(D3), ARRAYTOTEXT(DAY(EOMONTH(C3,1+SEQUENCE(,MONTH(D3)-MONTH(C3)-1,0,1))),0)&","&0),""),",")
E5:L5	E5	=ROUND(B3*(E4#/SUM(E4#)),2)
Dynamic array formulas.