Hello all. I have a bunch of data with delivery dates and I am trying to get some per week data. However, obviously some of the delivery dates are middle of the week and need to be prorated for week x and week x+1. I am able to get the week using the =weeknum() function but am unsure how to prorate the data per week based on week. anyone have any ideas?
Yes: Post a workbook with an example and desired result.
Entia non sunt multiplicanda sine necessitate
The week starts on friday. Since the dates will overlap weeks I need to somehow prorate units for weeks. For example, Shipment 1 will have units for both week 47 and 48
Last edited by DCRAIG3389; 11-18-2015 at 01:28 PM.
Only you can see Shipment 1; we are left to wonder.
Good luck.
sample.xlsxsorry, attached is the sample
Row\Col G4: =SUMIF($D$4:$D$7, F4, $B$4:$B$7)
I'm not sure what you want so have given you two possibilities as I see the problem.
1. The total for each week
Formula:
Please Login or Register to view this content.
2. The total for each week as the week progresses
Formula:Please Login or Register to view this content.
Shipment Quantity ETA Week # Total Units/Wk Additive Units/week
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
what i am looking for is the total of the units in the week. For example, if week 47 is 11/21-11/27 and i get a delivery on 11/24 of 10,000 units. I will only have 5,714 units for that week and the remainder will be for week 48.
This makes no sense to me at all. I'm out.
11/21/15 is a Saturday; you said your week starts on Friday ...?For example, if week 47 is 11/21-11/27
Row\Col D4: =WEEKNUM(C4, $D$2) E4: =ROUND(B4*(8 - WEEKDAY(C4, $D$2))/7, 0) F4: =B4-E4 I4: =SUMIF($D$4:$D$7, H4, $E$4:$E$7) + SUMIF($D$4:$D$7, H4-1, $F$4:$F$7)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks