[Solved] Weekly Sum of Production in Excel

[mfaisal.ce]

Dear,

I have 2 columns
column b has months from jan to dec and column C has production values on monthly basis.
i need to calculate weekly production based on date given in A1.
Week starts from Sunday and ends in Saturday.

For example
consider A1 is 1st july 2024. it means the week is 30-06-24 sunday to 06-07-24 saturday. formula should sum the values of 1 day of june production and 6 days of july production to give output as weekly production.

regads,

 

[shamimarita]

Dear,

I have 2 columns
column b has months from jan to dec and column C has production values on monthly basis.
i need to calculate weekly production based on date given in A1.
Week starts from Sunday and ends in Saturday.

For example
consider A1 is 1st july 2024. it means the week is 30-06-24 sunday to 06-07-24 saturday. formula should sum the values of 1 day of june production and 6 days of july production to give output as weekly production.

regads,

Hello Faisal,

First, create a Daily Production table using the values of Months and Values.
In Column D: Total days in each month (31, 29, 31, etc.)
In Column E: Daily production values: =C2/D2

Then, you need to calculate the starting date of the week (Sunday):
Use the following formula to find the previous Sunday from the given date in A1: = A1 - WEEKDAY(A1, 2)

Finally, use the following formula to sum the production values for the week, considering both the previous and current month's daily production values:
=IF(MONTH(F2)=MONTH(F2+6), E2*7, E2*(DAY(EOMONTH(F2,0))-DAY(F2)+1) + E3*(7-(DAY(EOMONTH(F2,0))-DAY(F2)+1)))




Download the Excel File:

 

[mfaisal.ce]

Dear,
Thanks for your reply but it is not working or may be i am not able to understand. i will explain agian and attach my original file for correction.

Sheet- Summary
cell: E2:E7 (I need to calculate Budgeted production for each product in B4:B7 but only for week (Sunday to Saturday).
Date: A1 is having the date and it will change daily.
Each Month Budgeted Target is already allocated in B11:B26.
Consider Each month 30 days equally.

i tried to rearrange the formula u provided in my sheet but unable to get the results.

Kindly guide.

regards,

 

[mfaisal.ce]

Hello ,
I m waiting for the response.
Kindly update

 

[mfaisal.ce]

Hello ,
I m waiting for the response.
Kindly update

 

[shamimarita]

Hello ,
I m waiting for the response.
Kindly update

Hi,

I am working with your formula.

 

[shamimarita]

Dear,
Thanks for your reply but it is not working or may be i am not able to understand. i will explain agian and attach my original file for correction.

Sheet- Summary
cell: E2:E7 (I need to calculate Budgeted production for each product in B4:B7 but only for week (Sunday to Saturday).
Date: A1 is having the date and it will change daily.
Each Month Budgeted Target is already allocated in B11:B26.
Consider Each month 30 days equally.

i tried to rearrange the formula u provided in my sheet but unable to get the results.

Kindly guide.

regards,

Hello Faisal,

I updated the previous formula to calculate weekly production dynamically. To use this formula you will need to use the TEXT function in your Month column.As without using the same date format MATCH function cannot fetch the exact values.
Formula:
=TEXT(B2, "mmm-yyyy")

Formula:
=IF(MONTH(A2 - WEEKDAY(A2, 2)) = MONTH(A2 - WEEKDAY(A2, 2) + 6),INDEX(D213, MATCH(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C2:C13, 0)) / 30 * 7,(INDEX(D213, MATCH(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C2:C13, 0)) / 30 * (DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1)) +(INDEX(D213, MATCH(TEXT(A2 - WEEKDAY(A2, 2) + 7, "mmm-yyyy"), C2:C13, 0)) / 30 * (7 - (DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1))))

You can use VLOOKUP instead of INDEX-MATCH. But the criteria for the Date format will be the same.
=IF(
MONTH(A2 - WEEKDAY(A2, 2)) = MONTH(A2 - WEEKDAY(A2, 2) + 6),
VLOOKUP(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C213, 2, FALSE) / 30 * 7,
(
VLOOKUP(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C213, 2, FALSE) / 30 *
(DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1)
) +
(
VLOOKUP(TEXT(A2 - WEEKDAY(A2, 2) + 7, "mmm-yyyy"), C213, 2, FALSE) / 30 *
(7 - (DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1))
)
)

Download the Excel File and update your main sheet according to this update.

 

[mfaisal.ce]

Thanks a lot. It worked.

regards,

 

[shamimarita]

Thanks a lot. It worked.

regards,

You are most welcome. I'm glad to hear that it worked. Let's continue to help each other out. If you need any further assistance, feel free to reach out. Have a great day!