Forum Discussion
Allocate integer values over specified months
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/m-p/154139/page/2
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!
See the attached example. I changed the values in F1:Q1 to dates.
- iali4920Copper Contributor
HansVogelaar 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!