[Solved] Weekly Sum of Production in Excel

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,
 

Attachments

  • Book1.xlsx
    12 KB · Views: 3
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)))

Weekly Production.png


Download the Excel File:
 

Attachments

  • Get Weekly Production.xlsx
    16.6 KB · Views: 4
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,
 

Attachments

  • Production Report-2 .xlsx
    325 KB · Views: 4
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")
Format Month.png
Formula:
=IF(MONTH(A2 - WEEKDAY(A2, 2)) = MONTH(A2 - WEEKDAY(A2, 2) + 6),INDEX(D2:D13, MATCH(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C2:C13, 0)) / 30 * 7,(INDEX(D2:D13, 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(D2:D13, 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))))
Final result.png

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"), C2:D13, 2, FALSE) / 30 * 7,
(
VLOOKUP(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C2:D13, 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"), C2:D13, 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.
 

Attachments

  • Updated Weekly Production.xlsx
    12.9 KB · Views: 4
Last edited:

Online statistics

Members online
0
Guests online
57
Total visitors
57

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top