Monthly demand to weekly demand

[yuvaraj859]

Hi ,
How to split the year or monthly demand to weekly demand for the every month

for example - Dec monthly demand is 5648 , working days is 27 ( excluding sunday ) avg per day is 209, 1st week should be 837, 2nd wk -1255, 3rd wk -1255, 4th wk -1255 , 5th wk -1046.

like this every month how can i spilt ,

can anyone tell any formula or macros for this

 

[Toadstool]

Hi yuvaraj859,

I assume your weeks must run Mon to Sun, hence the short first week of December 2021.

You can condense some calculations but I don't know how your data looks and how you want results presented so here is an approach showing most of the working out.

yuvaraj.xlsx
	A	B	C	D	E	F	G	H
1	Demand		Week Start	01-Dec-21	06-Dec-21	13-Dec-21	20-Dec-21	27-Dec-21
2	5648		Week End	05-Dec-21	12-Dec-21	19-Dec-21	26-Dec-21	31-Dec-21
3	Daily Demand		Working Days	4	6	6	6	5
4	209.19		Working Days x Daily Demand	837	1255	1255	1255	1046
Sheet1

Cell Formulas
Range	Formula
E1:H1	E1	=D2+1
D2:H2	D2	=MIN(D1+(7-WEEKDAY(D1,11)),EOMONTH(D1,0))
D3:H3	D3	=NETWORKDAYS.INTL(D1,D2,11)
D4:H4	D4	=ROUND(D3*$A$4,0)
A4	A4	=ROUND(A2/NETWORKDAYS.INTL(D1,EOMONTH(D1,0),11),2)

 

[yuvaraj859]

ok thank you ..

tell me Dec monthly demand is 5648 working days is 27, jan demand 4000 working days is 22 days.

 

[Toadstool]

The only two cells to change are shown in amber. If you enter 5648 and start 1 Dec 2021 then it shows the working days for each week as 4,6,6,6,5 so =27 and it shows that 5648 broken down by week, 837, 1255, 1255, 1255, 1046.

For January enter 4000 and change to start to 1/1/2022 and it shows the breakdown.

You really need to share how your sheet looks and where you want the calculations to go if you want further help.

yuvaraj.xlsx
	A	B	C	D	E	F	G	H	I
1	Demand		Week Start	01-Jan-22	03-Jan-22	10-Jan-22	17-Jan-22	24-Jan-22	31-Jan-22
2	4000		Week End	02-Jan-22	09-Jan-22	16-Jan-22	23-Jan-22	30-Jan-22	31-Jan-22
3	Daily Demand		Working Days	1	6	6	6	6	1
4	153.85		Working Days x Daily Demand	154	923	923	923	923	154
Sheet1 (2)

Cell Formulas
Range	Formula
E1:I1	E1	=D2+1
D2:I2	D2	=MIN(D1+(7-WEEKDAY(D1,11)),EOMONTH(D1,0))
D3:I3	D3	=NETWORKDAYS.INTL(D1,D2,11)
D4:I4	D4	=ROUND(D3*$A$4,0)
A4	A4	=ROUND(A2/NETWORKDAYS.INTL(D1,EOMONTH(D1,0),11),2)

 

[yuvaraj859]

Hi

i have pasted the data.

 

[Toadstool]

I'm not following. I'm not sure why the first week is week 2 starting 3 Jan, but the real challenge is I can't see how you derive the working days for each month or week.

You show week 6 has 1 working day but what about Feb 2nd to Feb 6th?
I see February has 24 working days stated but with the 5, 5 and 6 for Feb 7th to Feb 27th and potentially the 1 for the week started 31st Jan then that suggests week commencing 28th Feb has 7 working days, which would seem incorrect.

The January calculation is simple enough (404/22=18.36363636 so multiply each week's working days by that number gives 110.1818182 plus 73.45454545 plus 110.1818182 plus 91.81818182 plus 18.36363636 works us back to 404) but the split weeks won't always be just 1 working day so you need to know which are the actual working dates.

If you can confirm how the working days are reduced from the 6, Monday to Saturday, then you can perform the calculation. I assume it's some set of Public Holidays so if you provide the list of those dates it can be incorporated into the formula.

 

[Toadstool]

Below I'm showing a possible solution if the days not worked (unless Sunday) can be put in a list of dates. My list is almost random and put into column A, but you could move that to another Sheet. I've moved the week columns to be below the month input, just to make it easier to see on the mini-sheet.

I've made two rows for each week:

• Working Days 1st being the calculated working days for that week start up to that date plus 6 days or, if that takes us into the next month, then the end of that month.
• Working Days 2nd checks if the start week plus 6 takes us into the next month and if so it calculates working days from the 1st of that month to the start week plus 6 days.

In most cases Working Days 2nd will be zero but when a week spans two months, as in week commencing 31st Jan, then Working Days 1st is calculated as 1, for 31st Jan, and Working Days 2 is calculated as 5, being working days from 1st Feb to 6th Feb.

The Total Production Plan then becomes the average daily production for the month of the start week multiplied by the Working Days 1st plus the average daily production for the month of the start week plus 6 days multiplied by the Working Days 2nd.

yuvaraj.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Non-Workind Dates
2	01-Jan-22			Working Days		22	24	25	24	25	25	26	26	25	26	26	26
3	14-Jan-22			Customer Part No.		Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
4	15-Jan-22			155001AD		404	1526	1444	1550	1280	743	1000	1000	1000	1000	1000	1000
5	26-Jan-22
6	11-Mar-22
7	12-Mar-22
8	14-Apr-22
9	15-Apr-22			Date	03-Jan-22	10-Jan-22	17-Jan-22	24-Jan-22	31-Jan-22	07-Feb-22	14-Feb-22	21-Feb-22	28-Feb-22	07-Mar-22	14-Mar-22	21-Mar-22	28-Mar-22	04-Apr-22	11-Apr-22	18-Apr-22	25-Apr-22	02-May-22	09-May-22	16-May-22	23-May-22	30-May-22	06-Jun-22	13-Jun-22
10	14-May-22			Working Days 1st	6	4	6	5	1	6	6	6	1	4	6	6	4	6	4	6	6	6	5	6	6	2	6	5
11	14-Jun-22			Working Days 2nd	0	0	0	0	5	0	0	0	5	0	0	0	2	0	0	0	0	0	0	0	0	4	0	0
12	15-Aug-22			WeekNUM	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
13	02-Sep-22			Total Production Plan	110	73	110	92	336	382	382	382	352	231	347	347	360	388	258	388	388	307	256	307	307	221	178	149
14	02-Oct-22
15	24-Dec-22
16	25-Dec-22
2nd

Cell Formulas
Range	Formula
F2:Q2	F2	=NETWORKDAYS.INTL(F3,EOMONTH(F3,0),11,$A$2:$A$999)
F3	F3	=E9
G3:Q3	G3	=EOMONTH(F3,0)+1
F9:AB9	F9	=E9+7
E10:AB10	E10	=NETWORKDAYS.INTL(E9,MIN(E9+6,EOMONTH(E9,0)),11,$A$2:$A$999)
E11:AB11	E11	=IF(MONTH(E9)=MONTH(E9+6),0,NETWORKDAYS.INTL(DATE(YEAR(E9+6),MONTH(E9+6),1),E9+6,11,$A$2:$A$999))
E12:AB12	E12	=WEEKNUM(E9)
E13:AB13	E13	=ROUND((INDEX($F$4:$AAA$4,MATCH(E9,$F$3:$AAA$3,1))/INDEX($F$2:$AAA$2,MATCH(E9,$F$3:$AAA$3,1))*E10)+(INDEX($F$4:$AAA$4,MATCH(E9+6,$F$3:$AAA$3,1))/INDEX($F$2:$AAA$2,MATCH(E9+6,$F$3:$AAA$3,1))*E11),0)