Here is my issue, and I'm not sure where to start...
I have volumes by week from one data source, and the weeks always start on Sundays. I want to compare it to a data source that has volumes by month. Basically, I want to do the following:
1) Take my monthly shipments from my monthly source and divide by days. So if I have 31000 shipments in December, I would divide by 31 days to get 1000 shipments per day. (This isn't accurate, but since one system only tracks by month, this is the best I can do...)
2) Match up days with the weeks from the other system to compare shipments.
This is no problem, except that I'm not sure how to do a formula that splits it correctly so that if I have a week that spans two months (like 12/29-1/04). So if it's a week that splits, it applies the correct amount to December and then the correct amount to November.
I hate to ask so vaguely, but I've tried some things myself but none work. Just suggestions on how to get started are helpful.
Thanks!
Bookmarks