Allocate integer values over specified months

[iali4920]

Hi all,

I have a table in Excel that has a start date and an end date. I need to allocate a unit value spread over the months between the two dates. However I can only have whole units only, and if there is a remainder value, it is allocated to the final month in the window. This is the table range between JAN-DEC, which is for 2022 only.

If there is nothing in the Revised Units column, the value taken is from Units, otherwise Revised Units takes precedence.

The Unit Month Window calculates the amount of months using DATEDIF. The below table is the ideal state I would like to end up with.

Unit Start	Unit End	Units	Revised Units	Unit Month Window	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
01/01/2022	05/06/2022	100		5	20	20	20	20	20
04/03/2022	06/09/2022	82		6			14	14	14	14	14	12
02/05/2022	03/08/2022	32	34	3					12	12	10
01/12/2022	15/05/2023	15		5												3

I found this great thread regarding spreading values over multiple cells: https://techcommunity.microsoft.com/t5/excel/divide-a-quantity-as-whole-number-among-multiple-cells/...

but it doesn't quite answer the logic I'm looking for. I'm also keeping it in the table format as I would like to transfer the ability to a Sharepoint list using Calculated Columns, but Excel will do for now :)

I hope this makes sense!

[#2 · Accepted solution]

@iali4920 

See the attached example. I changed the values in F1:Q1 to dates.

[#3]

@Hans Vogelaar this is absolutely perfect! I've been scratching my head for a week trying to figure this out! I've modified the formulas to work in a table, and stripped the EOMONTH as Sharepoint Lists doesn't support this functionality, (replaced with a DATE formula that adds a month and subtracts a day to get the end of the month).

Thank you for the speediest reply!