Allocate the amount from the number of days

[ExcelNewbie2020]

how to allocate the amount from the number of days in a month from start to end.

below table shows an amount of 30000 to be proportionally allocated per month based on the number of days in each month starting from 13/01/2023 to 30/04/2023? thank you

Book1
	A	B	C	D	E	F	G	H
1	start	end	amount		expected result
2	13-01-23	30-04-23	30000		jan	feb	mar	apr
3					5,046.73	7,850.47	8,691.59	8,411.21
4
5
6
Sheet3

 

[Monty Norman]

Hello @ExcelNewbie2020

Dates are numbers so you can do something like:

You can say End date - Start date and you get 107.

Look at this page for more date information: Dates And Times In Excel

Hope this helps

Monty

 

[ExcelNewbie2020]

Hello @ExcelNewbie2020

Dates are numbers so you can do something like:
View attachment 87646
You can say End date - Start date and you get 107.

Look at this page for more date information: Dates And Times In Excel

Hope this helps

Monty

thank you, but that does not show the expected result..

 

[Dave Patton]

N.B. Jan-23 etc. are real dates like Jan 1 2023 formatted to show mmm-yy

T202303a.xlsm
	A	B	C	D	E	F	G	H	I
1									Allocate by days in month
2	Start	End	Amount	Total Days	Jan-23	Feb-23	Mar-23	Apr-23	May-23
3	13-Jan-23	30-Apr-23	30,000.00	108	5,277.78	7,777.78	8,611.11	8,333.33	0.00
4
2c

Cell Formulas
Range	Formula
D3	D3	=B3-A3+1
E3:I3	E3	=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))*$C3/$D3

 

[ExcelNewbie2020]

N.B. Jan-23 etc. are real dates like Jan 1 2023 formatted to show mmm-yy

T202303a.xlsm
	A	B	C	D	E	F	G	H	I
1									Allocate by days in month
2	Start	End	Amount	Total Days	Jan-23	Feb-23	Mar-23	Apr-23	May-23
3	13-Jan-23	30-Apr-23	30,000.00	108	5,277.78	7,777.78	8,611.11	8,333.33	0.00
4
2c

Cell Formulas
Range	Formula
D3	D3	=B3-A3+1
E3:I3	E3	=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))*$C3/$D3

thanks man, but it does not give the expected result..

 

[Dave Patton]

You stated "starting from 13/01/2023 to 30/04/2023".
If your count is different, just edit the formula!

 

[ExcelNewbie2020]

You stated "starting from 13/01/2023 to 30/04/2023".
If your count is different, just edit the formula!

yes your right i miscalculated the days from start to end date.. the formula works good.. thanks man..

 

[Dave Patton]

Thanks for the feedback.