SOLVED

Allocate integer values over specified months

%3CLINGO-SUB%20id%3D%22lingo-sub-3063765%22%20slang%3D%22en-US%22%3EAllocate%20integer%20values%20over%20specified%20months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3063765%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20in%20Excel%20that%20has%20a%20start%20date%20and%20an%20end%20date.%20I%20need%20to%20allocate%20a%20unit%20value%20spread%20over%20the%20months%20between%20the%20two%20dates.%20However%20I%20can%20only%20have%20whole%20units%20only%2C%20and%20if%20there%20is%20a%20remainder%20value%2C%20it%20is%20allocated%20to%20the%20final%20month%20in%20the%20window.%20This%20is%20the%20table%20range%20between%20JAN-DEC%2C%20which%20is%20for%202022%20only.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20nothing%20in%20the%20Revised%20Units%20column%2C%20the%20value%20taken%20is%20from%20Units%2C%20otherwise%20Revised%20Units%20takes%20precedence.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Unit%20Month%20Window%20calculates%20the%20amount%20of%20months%20using%20DATEDIF.%20The%20below%20table%20is%20the%20ideal%20state%20I%20would%20like%20to%20end%20up%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221237%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2278%22%3EUnit%20Start%3C%2FTD%3E%3CTD%20width%3D%2276%22%3EUnit%20End%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EUnits%3C%2FTD%3E%3CTD%20width%3D%22102%22%3ERevised%20Units%3C%2FTD%3E%3CTD%20width%3D%22149%22%3EUnit%20Month%20Window%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EJAN%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFEB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EMAR%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAPR%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EMAY%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EJUN%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EJUL%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAUG%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ESEP%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EOCT%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ENOV%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EDEC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%2F01%2F2022%3C%2FTD%3E%3CTD%3E05%2F06%2F2022%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04%2F03%2F2022%3C%2FTD%3E%3CTD%3E06%2F09%2F2022%3C%2FTD%3E%3CTD%3E82%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02%2F05%2F2022%3C%2FTD%3E%3CTD%3E03%2F08%2F2022%3C%2FTD%3E%3CTD%3E32%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%2F12%2F2022%3C%2FTD%3E%3CTD%3E15%2F05%2F2023%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20this%20great%20thread%20regarding%20spreading%20values%20over%20multiple%20cells%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fdivide-a-quantity-as-whole-number-among-multiple-cells%2Fm-p%2F154139%2Fpage%2F2%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fdivide-a-quantity-as-whole-number-among-multiple-cells%2Fm-p%2F154139%2Fpage%2F2%3C%2FA%3E%3C%2FP%3E%3CP%3Ebut%20it%20doesn't%20quite%20answer%20the%20logic%20I'm%20looking%20for.%20I'm%20also%20keeping%20it%20in%20the%20table%20format%20as%20I%20would%20like%20to%20transfer%20the%20ability%20to%20a%20Sharepoint%20list%20using%20Calculated%20Columns%2C%20but%20Excel%20will%20do%20for%20now%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20makes%20sense!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3063765%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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 StartUnit EndUnitsRevised UnitsUnit Month WindowJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
01/01/202205/06/2022100 52020202020       
04/03/202206/09/202282 6  141414141412    
02/05/202203/08/202232343    121210     
01/12/202215/05/202315 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 Replies
best response confirmed by iali4920 (New Contributor)
Solution

@iali4920 

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

@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!